x

StoreProcedure - Correct Syntax - Must declare scalar

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)
more ▼

asked Oct 02, 2012 at 05:06 PM in Default

avatar image

technette
1.4k 100 113 120

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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
more ▼

answered Oct 02, 2012 at 05:48 PM

avatar image

SirSQL
4.9k 4 5

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, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 11:01 PM SirSQL
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Oct 04, 2012 at 05:54 PM

avatar image

Blackhawk-17
12k 30 35 42

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x6

asked: Oct 02, 2012 at 05:06 PM

Seen: 1652 times

Last Updated: Oct 04, 2012 at 05:54 PM

Copyright 2016 Redgate Software. Privacy Policy