Dynamic Query in sql

  • Hi,

    Is there any possibility to declare variables and assign value to those variables dynamically in SQL Server 2008?

  • Yes it is possible.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Use sp_executesql with OUTPUT variables

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • I have following table structure

    table1

    (col1 int, col2 int, col3 int)

    and i have procedure as which returns sum as follows

    Create procedure test

    as

    begin

    Declare @col1total

    Declare @col2total

    Declare @col3total

    Select @col1total = Sum(col1)

    From table1

    Select @col2total = Sum(col2)

    From table1

    Select @col3total = Sum(col3)

    From table1

    End

    Is it possible to use a single dynamic query to assign values

    while trying

    Declare @counter int

    Declare @dynqry nvarchar(max)

    Select @counter = 1

    While @counter<=3

    Select @dynqry = 'SELECT @col'+CONVERT(varchar(5),@counter) +'=SUM('+CONVERT(varchar(5),@counter)'+') From table1'

    exec sp_executesql @dynqry

    am getting error must declare variable @col1

    Is this acheivable?

  • You need to declare and assign @col1 within the dynamic SQL. Or drop the @ if you just meant col1

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Exact code used is this

    Create proceduretest1

    As

    Begin

    Declare@col1totalint

    Declare@col2totalint

    Declare@col3totalint

    Declare @dynqrynvarchar(max)

    Declare @counterint

    Select@counter=1

    While@counter<=3

    Begin

    Select@dynqry='Declare @col'+convert(varchar(5),@counter)+'total int'

    Select@col'+CONVERT(varchar(5),@counter)+'total=SUM(col'+CONVERT(varchar(5),@counter)+') FROM Table1'

    exec sp_executesql @dynqry

    Select @counter=@counter+ 1

    End

    Select @col1total,@col2total,@col3total

    End

    But even if i declare and assign value after executing the query the output value is showing as null

  • vikramchander90 (10/18/2012)


    Exact code used is this

    Create proceduretest1

    As

    Begin

    Declare@col1totalint

    Declare@col2totalint

    Declare@col3totalint

    Declare @dynqrynvarchar(max)

    Declare @counterint

    Select@counter=1

    While@counter<=3

    Begin

    Select@dynqry='Declare @col'+convert(varchar(5),@counter)+'total int'

    Select@col'+CONVERT(varchar(5),@counter)+'total=SUM(col'+CONVERT(varchar(5),@counter)+') FROM Table1'

    exec sp_executesql @dynqry

    Select @counter=@counter+ 1

    End

    Select @col1total,@col2total,@col3total

    End

    But even if i declare and assign value after executing the query the output value is showing as null

    Why all this hassle?

    This will do exactly the same:

    Create proceduretest1

    As

    begin

    select sum(col1), sum(col2), sum(col3) from Table1

    end

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Was trying the same with dynamic query and wanted to check it if is possible by doing the same. Those values are needed in variables for some other purposes.

  • It can be done as dynamic, but that in no way makes it a good idea. If you need the totals for other shuff, then

    Create proceduretest1

    As

    begin

    Declare @col1total int, @col2total int, @col3total int;

    select @col1total = sum(col1), @col2total = sum(col2), @col3total = sum(col3) from Table1;

    /* do other stuff with totals here*/

    select @col1total, @col2total, @col3total;

    end

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ok thank you will use the same......

Viewing 10 posts - 1 through 9 (of 9 total)

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