SET @SQL = ...

Question

SET @SQL = ...

Amy.G

SSCrazy

Points: 2305
More actions
September 26, 2011 at 10:22 am

#390476

I have a general question about a certain type of SQL query. This is where instead of typing somehting like:
SELECT Var1
FROM Table1
Somethimg more like this is used:
DECLARE @sql
SET @sql = 'SELECT var1 FROM Table1'
EXEC @sql
What I'm looking for a article or white paper about what exactly this is and where/why do use it. I don't even know what it's called. I've read articles where this type of script was used, but why it was being used was not part of the article.
Does anyone know of a good overview of this concept. I'm baring all admitting I am totally clueless about this, so please be nice! 😀
Thank you,
Amy

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

When #1 doesn't work because you have a dynamic <server, db, owner, table> then you need #2.

The other real case is search dashboards where you have n optional parameters. In that case building a dynamic string is a better choice.

P.S. More often than not passing the table name as parameter is a bad idea, so plz stay away.

toddasd SSCertifiable Points: 6298 More actions · Answer 2

This will give you everything you want to know: http://www.sommarskog.se/dynamic_sql.html

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

bkubicek SSChampion Points: 10948 More actions · Answer 3

Amy.G (9/26/2011)
I have a general question about a certain type of SQL query. This is where instead of typing somehting like:
SELECT Var1
FROM Table1
Somethimg more like this is used:
DECLARE @sql
SET @sql = 'SELECT var1 FROM Table1'
EXEC @sql
What I'm looking for a article or white paper about what exactly this is and where/why do use it. I don't even know what it's called. I've read articles where this type of script was used, but why it was being used was not part of the article.
Does anyone know of a good overview of this concept. I'm baring all admitting I am totally clueless about this, so please be nice! 😀
Thank you,
Amy

You can use the exec statement to execute adhoc or dynamic sql statements. I think a better question is what are you trying to accomplish? If you need to make your where clause in your sql statement dynamic, there are some good ways of doing that without using exec statement. I would suggest caution and would only choose to use the exec statement as a last resort.

vince_sql Right there with Babe Points: 722 More actions · Answer 4

This is known as dynamic SQL.

It can be useful for reports which use Pivots, where the number of columns depends on a date range supplied by the user for example.

http://sqlvince.blogspot.com/[/url]

Amy.G SSCrazy Points: 2305 More actions · Answer 5

Thank you all. I am making my way through the article toddasd posted and learning a lot. So for the time being, this topic can be concidered resolved.

Note: I wish their was a "resolved" button.