Dynamic sql syntax issues for date time variable

Question

For this code, when I introduce the 2 date variables the code errors with an error.

The Error is

Msg 137, Level 15, State 1, Line 8
Must declare the scalar variable "@STARTDATE".
Msg 137, Level 15, State 1, Line 9
Must declare the scalar variable "@ENDDATE".

Any idea how to get round this as my brain is now officially dead ?

DECLARE 
    @Query1  AS NVARCHAR(MAX),
    @Startdate DATETIME,
    @Enddate DATETIME

SET @STARTDATE = '10-JAN-2012'
SET @ENDDATE = '11-JAN-2012'

SET @Query1 = '

;WITH CTE AS
(
    select COALESCE( PT.[description] , ''Grand Total'') AS [Transaction Type], 
           Sum (AI.PRICE_INC_VAT) AS [AMOUNT (ú) CREDIT],
           P.[DESCRIPTION] AS [PRODUCT TYPE]

From  [dbo].[T1] C 
join [dbo].[T2] S on S.[Customer_ID]=C.[Customer_ID] 
join [dbo].[T3] SO on SO.[SITE_ID]=S.[SITE_ID]
join [dbo].[T4] OI on OI.[ORDER_ID]=SO.[SITE_ORDER_ID]
left join [dbo].[T5] P on P.[PRODUCT_ID]=OI.[PRODUCT_ID]
JOIN [dbo].[T6] AI ON  AI.ORDER_ITEM_ID = OI.ORDER_ITEM_ID
JOIN [T7] JBAI ON JBAI.ACTION_ITEM_ID = AI.ACTION_ITEM_ID
JOIN T8 JB ON JB.JOB_BATCH_ID = JBAI.JOB_BATCH_ID
JOIN [T9] PA on PA.PAYMENT_ID=JB.PAYMENT_ID
LEFT JOIN [T10] CU ON JB.CUSTOMER_USER_ID = CU.CUSTOMER_USER_ID
JOIN [T11] PT ON PT.PAYMENT_TYPE_ID=PA.PAYMENT_TYPE_ID 
LEFT JOIN [T12] ON SU.SYS_USER_ID=JB.SYS_USER_ID

where P.[PRODUCT_CATEGORY_ID]= ''PADS'' 
    and C.COMPANY_ID= ''19992''
    and PA.DATE_RECEIVED BETWEEN @Startdate AND @Enddate 

group by PT.DESCRIPTION, P.DESCRIPTION
)'

Aaron Bertrand · Accepted Answer · 2013-02-25 19:59:13Z

6

Your parameters are out of scope with your sql query. You need to pass the parameters into the sql string. You will notice that you also have to cast the parameter as a varchar so it can be concatenated to the string:

DECLARE 
    @Query1  AS NVARCHAR(MAX),
    @Startdate DATETIME,
    @Enddate DATETIME

SET @STARTDATE = '10-JAN-2012'
SET @ENDDATE = '11-JAN-2012'

SET @Query1 = '

;WITH CTE AS
(
    select COALESCE( PT.[description] , ''Grand Total'') AS [Transaction Type], 
           Sum (AI.PRICE_INC_VAT) AS [AMOUNT (ú) CREDIT],
           P.[DESCRIPTION] AS [PRODUCT TYPE]

From  [dbo].[T1] C 
join [dbo].[T2] S on S.[Customer_ID]=C.[Customer_ID] 
join [dbo].[T3] SO on SO.[SITE_ID]=S.[SITE_ID]
join [dbo].[T4] OI on OI.[ORDER_ID]=SO.[SITE_ORDER_ID]
left join [dbo].[T5] P on P.[PRODUCT_ID]=OI.[PRODUCT_ID]
JOIN [dbo].[T6] AI ON  AI.ORDER_ITEM_ID = OI.ORDER_ITEM_ID
JOIN [T7] JBAI ON JBAI.ACTION_ITEM_ID = AI.ACTION_ITEM_ID
JOIN T8 JB ON JB.JOB_BATCH_ID = JBAI.JOB_BATCH_ID
JOIN [T9] PA on PA.PAYMENT_ID=JB.PAYMENT_ID
LEFT JOIN [T10] CU ON JB.CUSTOMER_USER_ID = CU.CUSTOMER_USER_ID
JOIN [T11] PT ON PT.PAYMENT_TYPE_ID=PA.PAYMENT_TYPE_ID 
LEFT JOIN [T12] ON SU.SYS_USER_ID=JB.SYS_USER_ID

where P.[PRODUCT_CATEGORY_ID]= ''PADS'' 
    and C.COMPANY_ID= ''19992''
    and PA.DATE_RECEIVED BETWEEN '+ convert(varchar(10), @Startdate, 120) 
    + ' AND '+ convert(varchar(10), @Enddate, 120) +' 

group by PT.DESCRIPTION, P.DESCRIPTION
)'

I am not sure why you are using dynamic SQL for this procedure though. This can be easily performed without the use of dynamic SQL:

;WITH CTE AS
(
    select COALESCE( PT.[description] , 'Grand Total') AS [Transaction Type], 
           Sum (AI.PRICE_INC_VAT) AS [AMOUNT (ú) CREDIT],
           P.[DESCRIPTION] AS [PRODUCT TYPE]

From  [dbo].[T1] C 
join [dbo].[T2] S on S.[Customer_ID]=C.[Customer_ID] 
join [dbo].[T3] SO on SO.[SITE_ID]=S.[SITE_ID]
join [dbo].[T4] OI on OI.[ORDER_ID]=SO.[SITE_ORDER_ID]
left join [dbo].[T5] P on P.[PRODUCT_ID]=OI.[PRODUCT_ID]
JOIN [dbo].[T6] AI ON  AI.ORDER_ITEM_ID = OI.ORDER_ITEM_ID
JOIN [T7] JBAI ON JBAI.ACTION_ITEM_ID = AI.ACTION_ITEM_ID
JOIN T8 JB ON JB.JOB_BATCH_ID = JBAI.JOB_BATCH_ID
JOIN [T9] PA on PA.PAYMENT_ID=JB.PAYMENT_ID
LEFT JOIN [T10] CU ON JB.CUSTOMER_USER_ID = CU.CUSTOMER_USER_ID
JOIN [T11] PT ON PT.PAYMENT_TYPE_ID=PA.PAYMENT_TYPE_ID 
LEFT JOIN [T12] ON SU.SYS_USER_ID=JB.SYS_USER_ID

where P.[PRODUCT_CATEGORY_ID]= 'PADS'
    and C.COMPANY_ID= '19992'
    and PA.DATE_RECEIVED BETWEEN @Startdate AND @Enddate

group by PT.DESCRIPTION, P.DESCRIPTION
)

edited Feb 25, 2013 at 19:59

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

answered Feb 25, 2013 at 19:53

Taryn

249k57 gold badges375 silver badges409 bronze badges

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

3 Comments

Ross Presser Over a year ago

I would add that what was asked so far doesn't seem to have any real need for dynamic SQL at all -- a stored procedure with \@Stardate and \@Enddate arguments would do fine.

Aaron Bertrand Over a year ago

Better yet, use sp_executesql so that you aren't concatenating strings.

Taryn Over a year ago

@RossPresser I was updating my answer to include that note when you commented.

Aaron Bertrand · Accepted Answer · 2013-02-25 19:57:02Z

2

If this really needs dynamic SQL, you should do the following:

-- USE UNAMBIGUOUS FORMATS PLEASE!

SET @STARTDATE = '20120110';
SET @ENDDATE = '20120111';

SET @Query1 = ';WITH CTE AS
...
    and PA.DATE_RECEIVED BETWEEN @STARTDATE AND @ENDDATE 
    group by PT.DESCRIPTION, P.DESCRIPTION
)';

EXEC sp_executesql 
  @Query1, 
  N'@STARTDATE DATETIME, @ENDDATE DATETIME',
  @STARTDATE, @ENDDATE;

However unless I'm missing something there is no need for dynamic SQL here.

answered Feb 25, 2013 at 19:57

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Collectives™ on Stack Overflow

Dynamic sql syntax issues for date time variable

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related