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 pubsgocreate procedure sp_foobar(	@foobar varchar(100))asexec (@foobar)gosp_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 @foobarexec('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.