Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2010
    Posts
    3

    Question Unanswered: dynamically declaration of variable

    I am getting problem in declaring the variables dynamically
    I am using the following script
    :
    declare @number as integer
    declare @i as integer
    set @number=(select count(distinct columnname) from Tablename)
    set @i=1
    while (@number>@i)
    begin
    declare @sqltmp nvarchar(1000)
    declare @sqltmp1 nvarchar(1000)
    declare @sqltmp2 nvarchar(1000)
    set @sqltmp = 'declare @abc' +cast(@i as varchar)+ ' as varchar(100)'
    exec SP_ExecuteSql @sqltmp
    set @sqltmp1 = 'set @abc' +cast(@i as varchar)+ '='+'Software'
    exec SP_ExecuteSql @sqltmp1
    set @sqltmp2 = 'print @abc' +cast(@i as varchar)
    exec SP_ExecuteSql @sqltmp2
    set @i=@i+1
    end

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Sun MySQL or Microsoft SQL Server?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2010
    Posts
    3

    its Microsoft SQL

    its Microsoft SQL

  4. #4
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Then I'll put it in the Microsoft SQL Server forum

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Each call to sp_executesql is completely independant of other calls. You could kludge this using the OUTPUT clause for the @params parameter to sp_executesql and "ferrying" the data along as you went.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    There are several ways to skin a cat and the use of dynamically-declared variables in SQL Server is not a viable concept.

    I assume you are doing this because you want to create variables of differing, data-dependent names on the fly.

    If this were my problem, I would create a table variable with the first column being a varchar containing this variable name. A second column might contain my data.

    You could read and write to this table as if you were assigning or reading a variable.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by PracticalProgram View Post
    If this were my problem, I would create a table variable with the first column being a varchar containing this variable name. A second column might contain my data.
    I'd do it this way to.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...but make it a temporary table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Is there a reason that you would recommend making use of a temporary table versus a table variable?
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A table variable would also be fine. I know temp tables are accessible by lower-scoped processing, and I think table variables are as well.

    Just did not want the OP creating a permanent database table to implement this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    I'm just a beginner at this but, from what I understand, temporary tables involve the tempDB database and all of the associated contention issues.

    I don't know if I a correct about this but, from what I understand, a table variable is even more localized than a temporary table.

    As such, I have started getting into the habit of using table variables rather than temporary tables.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  12. #12
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Can I ask why you want to declare the variables dynamically?

    What scope will the variables have ie if a variable is set to value x then can other programs read that variable's value?

    Mike

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    A table variable is more appropriate for small datasets.
    A temporary table is more efficient for large datasets, and can be more extensively indexed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yes and the optimizer will sometimes override your decision about whether things are in memory or in the tempdb.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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