Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2012
    Posts
    8

    Unanswered: getting value of the variable with its name assigned to a string

    hi,

    I am using Sybase ASE 15.x version. I have some variable @var1, @var2, @var3,...,@var40 in my stored procedure. i need to get a string to be executed with these 40 variables. How can i put this in a loop?

    select @sql = @sql + 'AND table.value1 = @var1'
    where @var1 is not null

    select @sql = @sql + 'AND table.value2 = @var2'
    where @var2 is not null
    .
    .
    .
    select @sql = @sql + 'AND table.value40 = @var40'
    where @var40 is not null

    i can do like this

    while @i <= 40
    begin
    select @sql = @sql + 'AND table.value'+covert(varchar(10),@i) || ' = @var'+convert(varchar(10),@i) where --- is not null
    end
    ---> here is what i am facing the issue.
    How will i get the value of the variables in a loop?

  2. #2
    Join Date
    Jan 2012
    Location
    Lisbon
    Posts
    115
    Provided Answers: 1
    Hi,

    If i understood correctly, what you need is dynamic sql. I did a small example with 3 variables.
    The final select is a SUM, that's why the conversion to int is there.

    Code:
    declare @var1 char(1), @var2 char(1), @var3 char(1)
    select @var1 = '1'
    select @var2 = '2'
    select @var3 = '3'
    
    declare @counter int
    select @counter=1
    declare @code varchar(150)
    select @code = 'select '
    
    while @counter <= 3
    begin
        select @code = @code + 'convert(int,@var' + convert(char(2),@counter) + ')'
        IF  @counter < 3 
             select @code = @code + ' + '
      
        select @counter = @counter+1
    end
    
    --Just to see if the select is what you desire
    select @code
    
    -- Result
    exec (@code)

    Hope it helps.
    Last edited by Catarrunas; 10-30-12 at 12:23.

  3. #3
    Join Date
    Oct 2012
    Posts
    8
    Hi,

    Thanks for the reply. I found the solution. Actually I was missing some braces in between.

Tags for this Thread

Posting Permissions

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