Post reply

passing parameters to exec()

  • May 16, 2006 at 12:51 pm

    #112908

    Hey all, I have a really quick question I'm a bit stumped on.

    I am dynamically creating a sql statement, don't ask why I just have to do it this way...

    Anyhow as I create it I'm appending variables into the string so as to have them evaluate to numbers in my string to be executed via exec().

    I send it (btw the value of @AScntr varchar(10) ='1' for this example)

    @sql = @sql + ', NullIf(Max((CASE WHEN sequenceNumber = ' + @AScntr + ' THEN actioncode else '''' END)),'''')

    and i get the following when I run print(@sql), and it give an error of must declare @AScntr when It tries to execute the string

    NullIf(Max((CASE WHEN sequenceNumber = @AScntr THEN actioncode else '' END)),'')

    but what I want is

    NullIf(Max((CASE WHEN sequenceNumber = 1 THEN actioncode else '' END)),'')

    Thanks in advance for your help!

    To help us help you read this[/url]For better help with performance problems please read this[/url]

  • May 16, 2006 at 1:01 pm

    #638255

    declare @AScntr varchar(10),

      @sql varchar(500)      

    select @AScntr = '1'  

    select @sql = ''

    select @sql = @sql + ', NullIf(Max((CASE WHEN sequenceNumber = ' + @AScntr + ' THEN actioncode else '''' END)),'''')'

    select @sql

  • May 16, 2006 at 1:08 pm

    #638257

    yeah I'm a bonehead... I was using SET instead of SELECT not that that wopuld make a difference, but I use 1 set of code for wwup to 8000 chars and another for over 8000.  I was changing the code in the wrong place. 

    Thanks.

    -Luke

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply