Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Dynamic Query in sql
Add to briefcase

Dynamic Query in sql Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
vikramchander90
Posted Thursday, October 18, 2012 2:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 10, 2013 2:33 AM
Points: 187, Visits: 26
Hi,

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 2,536, Visits: 4,355
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
Mark-101232
Posted Thursday, October 18, 2012 3:09 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 3:18 AM
Points: 1,500, Visits: 18,178
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
vikramchander90
Posted Thursday, October 18, 2012 3:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 10, 2013 2:33 AM
Points: 187, Visits: 26
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
GilaMonster
Posted Thursday, October 18, 2012 3:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 37,692, Visits: 29,951
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
vikramchander90
Posted Thursday, October 18, 2012 3:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 10, 2013 2:33 AM
Points: 187, Visits: 26
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
Eugene Elutin
Posted Thursday, October 18, 2012 4:04 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 2,536, Visits: 4,355
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
vikramchander90
Posted Thursday, October 18, 2012 4:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 10, 2013 2:33 AM
Points: 187, Visits: 26
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
GilaMonster
Posted Thursday, October 18, 2012 4:39 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:54 AM
Points: 37,692, Visits: 29,951
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
vikramchander90
Posted Thursday, October 18, 2012 11:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, January 10, 2013 2:33 AM
Points: 187, Visits: 26
ok thank you will use the same......
Post #1374637
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.