Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Dynamic SQL error

Author  Topic 

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-04-17 : 15:50:17
I have the following code that is pivoting a previous table. The table has two quarters, 2012Q2 and 2012Q3 (these will eventually change so that's why they are parameters at the moment). However, when I run the code, I get "Incorrect syntax near 'Q3'" which is the value of @SimQuarter. When I run the dynamic SQL code without the parameters (actual column names and PERIOD='2012Q3'), it runs just fine. What am I missing?? Any help will be appreciated!

	declare @HistQtr nvarchar(7)
set @HistQtr='2012Q2'
declare @SimQuarter nvarchar(7)
declare @SQL_UW nvarchar(max)

IF OBJECT_ID('AnalyticsV2.CM.CorrelationData_UW', 'U') IS NOT NULL
DROP TABLE AnalyticsV2.CM.CorrelationData_UW;


set @SimQuarter=(case when @HistQtr like '%Q1%' then left(@HistQtr,4)+'Q2'
when @HistQtr like '%Q2%' then left(@HistQtr,4)+'Q3'
when @HistQtr like '%Q3%' then left(@HistQtr,4)+'Q4'
else cast((cast(left(@HistQtr,4) as float)+1) as nvarchar(5)) +'Q1' end)

set @SQL_UW = 'select SIMULATION,' + @colNames +'
into AnalyticsV2.CM.CorrelationData_UW
from ( select SIMULATION, replace(PRODUCT,''_'','' '') as PRODUCT, Product_Description, Value
from AnalyticsV2.CM.CorrelationData
where Product_Description=''UW'' and PERIOD='+@SimQuarter+' ) d
pivot
(
min(Value)
for PRODUCT in (' + @colNames+ ')
) piv'
exec sp_executesql @SQL_UW

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-17 : 15:58:24
Change the line that starts with where to:
where Product_Description=''UW'' and PERIOD='''+@SimQuarter+''' ) d
Go to Top of Page

kotonikak
Yak Posting Veteran

92 Posts

Posted - 2013-04-17 : 16:16:42
Just noticed that and I was about to delete the post. Thanks againT
Go to Top of Page
   

- Advertisement -