Creating a function out of a select statement in SQL

Question

I have built a select statement to pick the date the participant was first recorded in the database. However I want to create a function out of this select statement to use it. I am fairly new to SQL and have never built a function before. My select statement looks like this:

Select DATEDIFF(day, (select min(startdatetime)
                        from GamePlay
                     ), enddatetime)
  from GamePlay
 where ParticipantID = '200'

My attempted function looks like this:

CREATE FUNCTION daysPlayed (@ParticipantID int)
RETURNS DateTime
AS
BEGIN
    Return DATEDIFF(day, (select min(startdatetime)
                            from GamePlay
                         ), enddatetime)
      from GamePlay
     where ParticipantID = @ParticipantID
END
GO

I want to call the function in a view to create a column though? — Bradley
– Bradley, Commented Sep 25, 2012 at 16:22

Radu Caprescu · Accepted Answer · 2012-09-25 16:23:12Z

3

DATEDIFF does not return a datetime. It returns an int.

Your function might look like this:

CREATE FUNCTION daysPlayed (@ParticipantID int)
RETURNS INT
AS
BEGIN
    DECLARE @result INT
    SELECT @result=DATEDIFF(day, (select min(startdatetime) from GamePlay), enddatetime) from GamePlay where ParticipantID = @ParticipantID
    RETURN @result

END

but i don't think it will do what you want it to do. Also, please note that using functions in selects over a large number of rows is a guarantee performance hit and you might not be able to use some of the indexes you set up in your tables.

answered Sep 25, 2012 at 16:23

Radu Caprescu

9931 gold badge8 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Afshin · Accepted Answer · 2012-09-25 16:29:20Z

1

I don't know exactly what you want but see this sample might be helpful

CREATE FUNCTION mDay (@Date nchar(10))
RETURNS nchar(2)
AS
BEGIN
RETURN substring(@Date,9,2)
END


SELECT     dbo.Courses.MID, dbo.Masters.ID, dbo.Masters.Name,COUNT(CASE dbo.mDay(CDate) WHEN '01' THEN 2 END) AS day1

FROM         dbo.Courses INNER JOIN
                  dbo.Masters ON dbo.Courses.MID = dbo.Masters.MCode
WHERE     (dbo.Courses.CLevel = @Kind) AND (dbo.Courses.CDate BETWEEN @Date1 AND @Date2)
GROUP BY dbo.Courses.MID, dbo.Masters.Name, dbo.Masters.Family, dbo.Masters.ID

answered Sep 25, 2012 at 16:29

Afshin

4,2253 gold badges28 silver badges35 bronze badges

Collectives™ on Stack Overflow

Creating a function out of a select statement in SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related