Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005
    Location
    london
    Posts
    9

    Unanswered: SQLServer declare new variable based on another variables value

    Hi, I'm a complete newbie to SQLServer and t-sql generally. What I want to do is create a new variable in a stored procedure based upon the value of another variable.

    eg in the loop below I want to create 10 new variables, called @var0,@var1,@var2 ...@var9



    declare @varname nvarchar(10)
    declare @i integer

    select @i=0

    while @i<10
    begin
    set @varname = cast(('@var'+cast(@i as char)) as nvarchar(10))
    set @i=@i+1
    end

    Does anyone know of a way to do this?

  2. #2
    Join Date
    May 2002
    Posts
    299
    short answer is 'no', it's not possible in the current scope. however, you can create a new scope and have the vars active there.

    e.g.

    Code:
    declare @i int, @sql1 nvarchar(1000),@sql2 nvarchar(1000),@sql3 nvarchar(1000)
    set @i=1
    while @i<=10
    begin
    set @sql1=isnull(@sql1+',','') + '@var'+cast(@i as sysname)+' nvarchar(10)'
    set @sql2=isnull(@sql2+' ','') + 'set @var'+cast(@i as sysname)+'='+cast(@i as sysname)
    set @sql3=isnull(@sql3+',','') + '@var'+cast(@i as sysname)
    set @i=@i+1
    end
    select @sql1,@sql2,@sql3
    
    exec('declare '+ @sql1+ ' ' + @sql2 + ' select ' + @sql3)
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Jul 2005
    Location
    london
    Posts
    9
    I appreciate your help, thankyou!

    one more question now - if i wanted to then generically reference my new variables whilst in the loop, how would I do that?
    While I can see that the third of the statements lists the new variable names, I can't see that i would be able to use

    select @var + cast(@i AS sysname)

    as even an idiot like me can see that its not gonig to work
    Last edited by fionalpicton; 10-26-05 at 11:31.

  4. #4
    Join Date
    May 2002
    Posts
    299
    As I said, it's not possible in the current scope. I.e. it's not possible to create a variable dynamically and set a value to it.
    --
    -oj
    http://www.rac4sql.net

  5. #5
    Join Date
    Jul 2005
    Location
    london
    Posts
    9
    thats so frustrating!

    The situation we have is that we are migrating from Access to SQLServer and my manager has asked me to start putting as much code as possible in sps, but I think I'll stick to Access/VBA as it seems to be so much more flexible that using sps in SQLserver - or am I missing something?

    anyway, thanks again for your help. Nice to know there are people out there SO much smarter then me

  6. #6
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by fionalpicton
    ... or am I missing something?

    Your manager sounds like a wise person by asking that much of the processing logic be moved to stored procedures, if the app queries will return essentially the same type of data per execution. A properly written sp will have an execution plan cached the first time it is called which can be reused on subsequent calls with virtually the same efficiency as the first call. I say well written, because if write a sp that acts like a menu selection (as an example), you will get an optimum path execution plan for only the first call. Any subsequent calls that would use a different table, column, or dataset will not recieve optimal execution.

    If you are pushing sql code from the app layer to the database engine, it has to recalculate the execution plan each and every time.

    -- This is all just a Figment of my Imagination --

  7. #7
    Join Date
    Oct 2005
    Posts
    5
    Quote Originally Posted by fionalpicton
    or am I missing something?
    I think you're missing something. T-SQL stored procedures seem "hard" to you because you're thinking in terms of procedural VB. T-SQL is set-based, so processing you may have done in a loop in VB for example would be better served as a single set operation (insert, update, etc).

    For your current query about variable variables, can you explain why you need them (perhaps with sample VBA code that ? I would suspect that you would be better off thinking in terms of tables instead, in that you could use a table variable to store multiple pieces of data rather than variable variables.

    For what it's worth, I agree with your manager.

Posting Permissions

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