Creating functions in SQL Server?

Question

I'm trying to create a function that will print game information when the user enters a starting date and an ending date as a parameter. It will show the game information if the game date is between the starting and end date. I decided to use a table valued function.

So far I have:

CREATE FUNCTION fn_gamedate
    (@InputStartDate DATETIME, 
     @InputEndDate DATETIME)
RETURNS TABLE
AS
    RETURN 
        (SELECT * 
         FROM Game 
         WHERE Game.[Date] Between @StartDate AND @EndDate)

The error I get when I try to run this is

Must declare the scalar variable "@StartDate".

I'm confused as to what I would declare the @startdate and @enddate as and where in the statement to declare it. Thanks!

marc_s · Accepted Answer · 2016-04-17 21:10:47Z

8

The parameter is called @InputStartDate, but in the code, you use @StartDate - make up your mind!

Try this:

CREATE FUNCTION fn_gamedate
    (@InputStartDate DATETIME,   <--------+
     @InputEndDate DATETIME)              | these two need to MATCH!
RETURNS TABLE                             | 
AS                                        |
    RETURN                                | 
        (SELECT *                         |
         FROM Game                        |
         WHERE Game.[Date] Between @InputStartDate AND @InputEndDate)

If you name your parameter @InputStartDate, then you must use the same name in your T-SQL statement!

answered Apr 17, 2016 at 21:10

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

Creating functions in SQL Server?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related