SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Dynamic Query in sql


Dynamic Query in sql

Author
Message
vikramchander90
vikramchander90
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 76
Hi,

Is there any possibility to declare variables and assign value to those variables dynamically in SQL Server 2008?
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12766 Visits: 5478
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
Mark Cowne
Mark Cowne
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6573 Visits: 25602
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




vikramchander90
vikramchander90
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 76
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229498 Visits: 46344
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


vikramchander90
vikramchander90
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 76
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
Eugene Elutin
Eugene Elutin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12766 Visits: 5478
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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
vikramchander90
vikramchander90
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 76
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)SSC Guru (229K reputation)

Group: General Forum Members
Points: 229498 Visits: 46344
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, 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


vikramchander90
vikramchander90
Old Hand
Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)Old Hand (352 reputation)

Group: General Forum Members
Points: 352 Visits: 76
ok thank you will use the same......
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search