StoreProcedure - Correct Syntax - Must declare scalar

Question

Can someone help me with the correct syntax for the following stored procedure query? I am trying to use the following query for a report and it works fine in the report designer's sqldatasource but I have to convert it to a stored procedure.

The Error is: Msg 102, Level 15, State 1, Procedure PricingProcedure, Line 16 Incorrect syntax near 'DateTime'.

and: Msg 137, Level 15, State 2, Procedure PricingProcedure, Line 53 Must declare the scalar variable "@StartDate".

@StartDate DateTime;
@EndDate DateTime;
@LicenseCode nvarchar(80);
@Status nvarchar(25);


SELECT     QUOTE_LINES.QuoteID, 
           QUOTE_LINES.LineNumber, 
           QUOTE_LINES.LicenseCode, 
           QUOTE.[Name], 
           QUOTE.RFQ, 
           QUOTE.QuoteDate, 
           QUOTE.RFQDueDate, 
           QUOTE_LINES.OnticPartID, 
           QUOTE_LINES.PartDesc, 
           QUOTE_LINES.Qty, 
           QUOTE_LINES.UnitPrice, 
           QUOTE_LINES.TotalPrice, 
           QUOTE_LINES.LeadTime, 
           QUOTE_LINES.DateCompleted, 
           QUOTE.SalesRep, 
           QUOTE.Buyer, 
           QUOTE.Phone, 
           QUOTE.Fax, 
           QUOTE.EMail, 
           QUOTE.PLMnotes, 
           QUOTE_LINES.Notes, 
           QUOTE_LINES.Status, 
           PartTBL.QTY_ON_HAND
FROM         QUOTE INNER JOIN
              QUOTE_LINES ON QUOTE.QuoteID = QUOTE_LINES.QuoteID INNER JOIN
                          (SELECT     ID, QTY_ON_HAND
                            FROM          TheDB.dbo.PART) AS PartTBL ON QUOTE_LINES.PartID = PartTBL.ID
WHERE     (QUOTE.QuoteDate BETWEEN @StartDate AND @EndDate) AND
                      (QUOTE_LINES.LicenseCode = @LicenseCode) OR
                      (QUOTE_LINES.Status = @Status)

Answer 1

0

You need to actually declare the variables ie DECLARE @StartDate DATATIME

If you want this as a stored procedure the following will work:

CREATE PROCEDURE GetQuotes
    @StartDate DATETIME ,
    @EndDate DATETIME ,
    @LicenseCode NVARCHAR(80) ,
    @Status NVARCHAR(25)
AS 
    SELECT  QUOTE_LINES.QuoteID ,
            QUOTE_LINES.LineNumber ,
            QUOTE_LINES.LicenseCode ,
            QUOTE.[Name] ,
            QUOTE.RFQ ,
            QUOTE.QuoteDate ,
            QUOTE.RFQDueDate ,
            QUOTE_LINES.OnticPartID ,
            QUOTE_LINES.PartDesc ,
            QUOTE_LINES.Qty ,
            QUOTE_LINES.UnitPrice ,
            QUOTE_LINES.TotalPrice ,
            QUOTE_LINES.LeadTime ,
            QUOTE_LINES.DateCompleted ,
            QUOTE.SalesRep ,
            QUOTE.Buyer ,
            QUOTE.Phone ,
            QUOTE.Fax ,
            QUOTE.EMail ,
            QUOTE.PLMnotes ,
            QUOTE_LINES.Notes ,
            QUOTE_LINES.Status ,
            PartTBL.QTY_ON_HAND
    FROM    QUOTE
            INNER JOIN QUOTE_LINES ON QUOTE.QuoteID = QUOTE_LINES.QuoteID
            INNER JOIN ( SELECT ID ,
                                QTY_ON_HAND
                         FROM   TheDB.dbo.PART
                       ) AS PartTBL ON QUOTE_LINES.PartID = PartTBL.ID
    WHERE   ( QUOTE.QuoteDate BETWEEN @StartDate AND @EndDate )
            AND ( QUOTE_LINES.LicenseCode = @LicenseCode )
            OR ( QUOTE_LINES.Status = @Status );
GO

answered Oct 02 '12 at 05:48 PM

SirSQL
4.6k ● 1 ● 3

Thank you for responding SirSQL,

When I declare the variables, I get no data

DECLARE @StartDate DATETIME; DECLARE @EndDate DATETIME; DECLARE @LicenseCode NVARCHAR(80); DECLARE @Status NVARCHAR(25);

Oct 02 '12 at 06:43 PM technette

Ok, I corrected the following and I get data but still not in the date range. The QuoteDate Column is smalldatetime. How do I convert it?

@StartDate datetime = NULL,

@EndDate datetime = NULL,

@LicenseCode NVARCHAR(80),

@Status NVARCHAR(25)

AS

IF @StartDate IS NULL

BEGIN

SELECT @StartDate = GETDATE() -1

END

IF @EndDate IS NULL

BEGIN

SELECT @EndDate = GETDATE()

END

Oct 02 '12 at 07:15 PM technette

It will do an implicit conversion on the datetime value, but you can always declare the variables as smalldatetime. I recommend you use dateadd(d, -1, getdate()) rather than getdate()-1. Although your version works fine it's better to explicitly define these things. Also, avoid between, use >= and < or > and <=

Oct 02 '12 at 07:23 PM SirSQL

I tried > = and < = but I'm still getting dates that are out of range.

I executed the stored procedure using '20120101' and '20120501' for startdate and enddate respectively but and getting:

2005-12-21 00:00:00 in my Quotedate ???

Oct 02 '12 at 08:17 PM technette

Add a print statement and output the values of the startdate and enddate variables so that you can see what they are.

Also, take a look at your data, and use values that you know exist so that you can easily validate results.

Oct 02 '12 at 11:01 PM SirSQL

(comments are locked)

Answer 2

0

You're getting out of date ranges because of the OR clause:

OR ( QUOTE_LINES.Status = @Status );

You say give me results that (fit within my date and match my @LicenseCode) OR match my @Status.

If you change it to an AND then all three conditions must be true for a result. Right now the first two are taken as one unit and the OR clause is separate.

answered Oct 04 '12 at 05:54 PM

Blackhawk-17
10.5k ● 23 ● 29 ● 34

(comments are locked)

StoreProcedure - Correct Syntax - Must declare scalar

Follow this question

Related Questions