CODEWITHSUNDEEP
sql-serverfunctiont-sqlparameters
Hailey
Hailey

Reputation: 69

Creating functions in SQL Server?

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!

Upvotes: 2

Views: 86

Answers (1)

marc_s
marc_s

Reputation: 755321

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!

Upvotes: 8

Related Questions