asked
variable replacement using sp_executesql
Hi there, I'm on MS SQL 2008 and thought I had the hang of using sp_executesql as I have used it successful with other queries using the params as search conditions in the where clauses. So now I,ve moved on into CTE's and wanted to build up the cte dynamacially and took the same approach. However the variables dont seem to substitute correctly. Can anyone spot whats up. I have stripped this down to s simple example. So remember I cut this down but it wont substitute the variables. If I substitute these for actual values it works. DECLARE @dateCastLength VARCHAR(2), @myTable VARCHAR(50) --dynamic SQL DECLARE @sql NVARCHAR(MAX), @paramlist NVARCHAR(4000) SET @myTable = 'TickData.dbo.EURUSD' -- table to query SELECT @dateCastLength = '10' --length to format a date -- build the dynamic SQL to use SELECT @sql = 'WITH cte AS ( SELECT CONVERT(VARCHAR(@dateCastLength), QuoteDateTimeUTC, 120) as myconvertedDate from @myTable ) SELECT * FROM cte' -- now set the params SELECT @paramlist = '@myTable VARCHAR(50), @dateCastLength VARCHAR(2)' print @sql EXEC master.sys.sp_executesql @sql, @paramlist, @myTable, @dateCastLength
You can't pass a table name as a variable in that fashion. You'll still have to build the string and replace the variable with the table name such that when the query executes, it has a table to reference. Otherwise, it will just fail.
@Grant is right. And you are close. Replace @myTable with: ' + @myTable + ' and do not pass the @myTable variable anymore
thanks lads, yes ozamora, I,ve just coped this from reading the last paragraph at the bottom of
http://www.sommarskog.se/dyn-search-2008.html how ever the replacement for the @dateCastLength doesnt seem to work Will variable substitution work in all places except the FROM clause? thanks Mick - CONVERT(VARCHAR(@dateCastLength), QuoteDateTimeUTC, 120) as myconvertedDate
thanks Ozamora for the very quick response.. I have amended the code for now like so WITH cte AS ( SELECT CONVERT(VARCHAR(@dateCastLength), QuoteDateTimeUTC, 120 as convertedDate, QuoteDateTimeUTC as myconvertedDate FROM TickData.dbo.EURUSD.' + @myTable + ' )..... but it doesnt like the @dateCastLength. If I replace the @dateCastLength with a number like 16 eg CONVERT(VARCHAR(16), QuoteDateTimeUTC, 120) as convertedDate, then it works I have @dateCastLength declared as a varchar so should it be an int instead.. oh ps... when you said variable sub can be used anywhere... thats except in the FROM clause.. is this correct...
thanks Ozamora -- sorry to still wreck your head :-) but this works when I use the replicate to pump out a string a few times so why doesnt it work in CONVERT(VARCHAR(@dateCastLength), QuoteDateTimeUTC, 120 as convertedDate AS ( SELECT REPLICATE (N''ABC'',@dateCastLength) as convertedDate, QuoteDateTimeUTC as myconvertedDate FROM TickData.dbo.' + @myTable + ' )
Geeez Ozamora... you rock... top class lol. I know the code is really simple but this isnt the real implementation honest... I'm using CTE's to work out candle stick charts and aggregate data on different time spans and currencies in one stored proc... so thanks a million... this works great... regards Mick
aahh duh... sorry Ozamora... the cast is done when building up the sql so this also works.. CONVERT(VARCHAR(' + @dateCastLength + '), QuoteDateTimeUTC, 120) with out the cast. What I was trying to do was pass the @dateCastLength in as a parameter but will leave it like this for now as I seem to have wrecked everyones head.. including my own... thanks for all the help
