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.
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...
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