Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Query in sql Expand / Collapse
Author
Message
Posted Thursday, October 18, 2012 2:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:48 PM
Points: 236, Visits: 37
Hi,

Is there any possibility to declare variables and assign value to those variables dynamically in SQL Server 2008?
Post #1374201
Posted Thursday, October 18, 2012 3:07 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
Yes it is possible.


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

How to post your question to get the best and quick help
Post #1374205
Posted Thursday, October 18, 2012 3:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, November 11, 2014 3:53 AM
Points: 1,678, Visits: 19,555
Use sp_executesql with OUTPUT variables

____________________________________________________

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1374206
Posted Thursday, October 18, 2012 3:20 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:48 PM
Points: 236, Visits: 37
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?
Post #1374210
Posted Thursday, October 18, 2012 3:30 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 40,390, Visits: 36,823
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 2008, MVP
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

Post #1374217
Posted Thursday, October 18, 2012 3:34 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:48 PM
Points: 236, Visits: 37
Exact code used is this
Create procedure test1
As
Begin
Declare @col1total int
Declare @col2total int
Declare @col3total int
Declare @dynqry nvarchar(max)
Declare @counter int

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
Post #1374221
Posted Thursday, October 18, 2012 4:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 3:45 AM
Points: 2,873, Visits: 5,188
vikramchander90 (10/18/2012)
Exact code used is this
Create procedure test1
As
Begin
Declare @col1total int
Declare @col2total int
Declare @col3total int
Declare @dynqry nvarchar(max)
Declare @counter int

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 procedure	test1
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!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1374239
Posted Thursday, October 18, 2012 4:27 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:48 PM
Points: 236, Visits: 37
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.
Post #1374257
Posted Thursday, October 18, 2012 4:39 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 2:01 PM
Points: 40,390, Visits: 36,823
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 procedure	test1
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 2008, MVP
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

Post #1374262
Posted Thursday, October 18, 2012 11:18 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, June 23, 2014 11:48 PM
Points: 236, Visits: 37
ok thank you will use the same......
Post #1374637
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse