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"
I know removing the 'exec' command doesn't launch the query.
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:
create procedure sp_foobar
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:
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.