variable replacement using sp_executesql

Question

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

Answer 1

0

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

answered Oct 25 '10 at 07:41 AM

Doiremik
71 ● 4 ● 6 ● 7

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

Oct 25 '10 at 07:48 AM Doiremik

no prob. Good to learn something new everyday eh?

Oct 25 '10 at 07:50 AM ozamora

(comments are locked)

Answer 2

0

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

answered Oct 25 '10 at 07:10 AM

Doiremik
71 ● 4 ● 6 ● 7

Yeah, as a variable, not as an object replacement.

Oct 25 '10 at 07:21 AM ozamora

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 + '
          )

Oct 25 '10 at 07:25 AM Doiremik

It might need a literal. I have not experienced this before. You might be able to workaround:

CONVERT(VARCHAR(' + CAST(@dateCastLength AS VARCHAR) + '), QuoteDateTimeUTC, 120 as convertedDate

Oct 25 '10 at 07:33 AM ozamora

(comments are locked)

Answer 3

0

@Grant is right. And you are close.

Replace @myTable with:

' + @myTable + '

and do not pass the @myTable variable anymore

answered Oct 25 '10 at 06:32 AM

ozamora
1.4k ● 2 ● 3 ● 5

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

Oct 25 '10 at 06:51 AM Doiremik

You are forgetting the datatype in CONVERT. You can pass variables anywhere in the query.

Oct 25 '10 at 06:57 AM ozamora

(comments are locked)

Answer 4

0

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.

answered Oct 25 '10 at 06:30 AM

Grant Fritchey ♦♦
61.9k ● 12 ● 19 ● 66

(comments are locked)

variable replacement using sp_executesql

Follow this question

Related Questions