Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: SQL Server
Expertise: Beginner
Jul 20, 2000

Creating and Executing a Variable as a Query

Question:
How do I create a stored procedure that will construct and execute a query from its parameters?

At the heart I'd imagine something like this, for simplicity's sake:

declare @foobar varchar(255)
select @foobar = "select * from aTable"
exec @foobar
I know removing the 'exec' command doesn't launch the query.

Answer:
You actually are on track. You need to make only one slight change—place parentheses around @foobar, like this: exec(@foobar).

So to make your example complete, try the following:

use pubs
go
create procedure sp_foobar
(
	@foobar varchar(100)
)
as
exec (@foobar)

go
sp_foobar 'select * from authors'
This technique has some gotcha's though. First of all, you can't create a dynamic SQL statement that sets a variable in the stored procedure. So the following statement won't work:
declare @foobar

exec('select @foobar = ...')
You can also run into problems if you are trying to build a command string from fields or sources that have either single quotes or double quotes in them.

However, dynamic SQL can be very useful in some situations. On a recent project, I needed to save the changes made in one database and apply them to another. I'm going to skip the details of why I couldn't simply use replication. I created an audit trail that had the name of the table, primary key value, field name, and old and new values. Using dynamic SQL, I was then able to reconstruct the necessary update statement and apply it to the second database.

DevX Pro
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date