Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: Assignment of variable within a Dynamic SQL Statement Fails. Why?

    -- Could you please help me with using dynamic SQL in Sybase. Thanks.
    --
    -- I'm trying to get the @Target to be assigned the literal string of "Hello world". It fails on the EXECUTE statement.

    -- You should be able to cut and paste this as is to see the error message.

    Declare @Cnt Int,
    @Parm_1 Varchar(20),
    @Parm_2 Varchar(20),
    @Target Varchar(254)

    Select @Parm_1 = 'Hello'
    Select @Parm_2 = 'world'
    Select @Cnt = 1

    Select @Parm_1 'Parm_1'

    Select @Temp_String = "Select @Target = @Parm" + Convert(VarChar(2), @Cnt) + ' ' + @Parm_2

    -- At this point @Temp_String contains "Select @Target = @Parm1 world"
    Select @Temp_String "@Temp_String"

    -- This fails; it complains about @Parm1 ??? .. how to fix it ??
    -- error(137) : Must declare variable '@Parm1'.
    Execute (@Temp_String)

    Select @Target 'Target'

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    OK with 12.5.4
    With older versions
    declare @var int
    create #temp table ...
    exec ('select x into #temp from ...')
    select @var=x from #temp
    drop table #temp

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    Thanks. We are on 12.5.3 ... need to wait a few more months until they upgrade.

  4. #4
    Join Date
    Mar 2007
    Posts
    25
    Hi,

    Hope the below piece of code helps you.

    declare @Parm_1 varchar(20),@Parm_2 varchar(20),@temp_string varchar(150)
    select @Parm_1 = 'Hello'
    select @Parm_2 = 'World'
    select @temp_string = "declare @target_string varchar(40) select @target_string =" + "'" + @Parm_1 + " " + @Parm_2 + "' select
    @target_string"
    exec (@temp_string)

    One disadvantage with the above code is that the target_String cannot be used elsewhere.

    Regards,
    Poornima.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •