Executing stored procedure in T-SQL with dates as parameters

Question

I need to make a stored procedure which takes supplierName, startDate and endDate as parameters and calculates the total income of that supplier for the given interval. I am using Northwind. The procedure looks as follows:

CREATE PROC usp_GetTotalIncomeByPeriod (
    @companyName nvarchar(40),
    @startDate date,
    @endDate date)
AS
    BEGIN
        SELECT SUM(od.UnitPrice * od.Quantity * (1 - od.Discount))
        FROM Orders o
            JOIN [Order Details] od
                ON o.OrderId = od.OrderId
            JOIN Products p
                ON od.ProductID = p.ProductID
            JOIN Suppliers s
                ON p.SupplierID = s.SupplierID
        WHERE s.CompanyName = @companyName
            AND (o.OrderDate BETWEEN @startDate AND @endDate);
    END
GO

The problem is that I can't actually execute it using the following invokation:

EXEC dbo.usp_GetTotalIncomeByPeriod @companyName = 'Exotic Liquids', 
                                    @startDate = DATEFROMPARTS(1900, 01, 01),
                                    @endDate = DATEFROMPARTS(2000, 1, 1);

The DATEFROMPARTS function seems to work fine on its own, but i cant manage to make it work as a parameter. It constantly gives me 'Incorrect syntax near 1900'. What do I miss?

You can't use function calls in stored procedure parameter lists. Just use a string literal such as `'1900-01-01'`. — Martin Smith, Dec 06 '14 at 20:06

score 6 · Accepted Answer · edited Dec 06 '14 at 20:01

the braces of the function

  DATEFROMPARTS()

is causing the error. So either pass date directly without the function or use a PLSQL block to do so by storing the result of the DATEFROMPARTS() in variable and then pass the variable to the stored procedure.

 DECLARE    
    @return_value int,
    @vDate1 date,
    @vDate2 date

SET @vDate1=DATEFROMPARTS(1900, 01, 01);
SET @vDate2=DATEFROMPARTS(2000, 01, 01);

EXEC @return_value = dbo.usp_GetTotalIncomeByPeriod 
                                @companyName = 'Exotic Liquids', 
                                @startDate = @vDate1,
                                @endDate = @vDate2;

Duh.. the only thing I did not try. Simple, and yet quite obvious now. Thank you! — zhulien, Dec 06 '14 at 20:08

Executing stored procedure in T-SQL with dates as parameters

1 Answers1