SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Dynamic SQL error
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kotonikak
Yak Posting Veteran

66 Posts

Posted - 04/17/2013 :  15:50:17  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3568 Posts

Posted - 04/17/2013 :  15:58:24  Show Profile  Reply with Quote
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

66 Posts

Posted - 04/17/2013 :  16:16:42  Show Profile  Reply with Quote
Just noticed that and I was about to delete the post. Thanks againT
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000