Printable View
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure? Thanks.
If you want to do that, what is the point of having a stored procedure at all?
I recommend explaining what you actually want to do (rather than how you want to do it), as there is likely to be a much better way to do what you want.
It's certainly possible. You'd just pas the statement in as a parameter (I'd suggest a VarChar(Max)) and execute it as dynamic sql using sp_ExceuteSQL.
I agree with Si, though. This has no advantages at all over simply executing the statement. It won't perform any better, it will require all the same table permissions and security, it is, in effect, exactly the same as having no stored procedure at all.
Due to the nature of our platform, sometimes it is necessary to run dynamic sql statements... that said, i've never had the need to pass the entire query as a parameter, i'll try to pass the individual pieces over, and let the target sproc assemble the query and execute it. At anyrate, it's possible.
-tg