Dynamic Query in sql
-
Hi,
Is there any possibility to declare variables and assign value to those variables dynamically in SQL Server 2008?
-
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 recoverabilityWe 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 thisCreate 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
-
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 proceduretest1As
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 recoverabilityWe 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