x

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
more ▼

asked Oct 25, 2010 at 06:25 AM in Default

Doiremik gravatar image

Doiremik
71 6 6 7

(comments are locked)
10|1200 characters needed characters left

4 answers: sort newest
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
more ▼

answered Oct 25, 2010 at 07:41 AM

Doiremik gravatar image

Doiremik
71 6 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, 2010 at 07:48 AM Doiremik
no prob. Good to learn something new everyday eh?
Oct 25, 2010 at 07:50 AM ozamora
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 25, 2010 at 07:10 AM

Doiremik gravatar image

Doiremik
71 6 6 7

Yeah, as a variable, not as an object replacement.
Oct 25, 2010 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, 2010 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, 2010 at 07:33 AM ozamora
(comments are locked)
10|1200 characters needed characters left

@Grant is right. And you are close.

Replace @myTable with:

' + @myTable + '

and do not pass the @myTable variable anymore
more ▼

answered Oct 25, 2010 at 06:32 AM

ozamora gravatar image

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, 2010 at 06:51 AM Doiremik
You are forgetting the datatype in CONVERT. You can pass variables anywhere in the query.
Oct 25, 2010 at 06:57 AM ozamora
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Oct 25, 2010 at 06:30 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
101k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x51

asked: Oct 25, 2010 at 06:25 AM

Seen: 2214 times

Last Updated: Oct 25, 2010 at 06:25 AM