i m creating a stored procedure and i want it to fetch a field from a table which will contain a query and execute it. is it possible to do that ??
for example : there's a table name MyTable and contains a column named LIST
List contains the query text (e.g update table2 set id = 1)
now i wanna fetch field from a table (which is easy) but how can i execute it after retrieval
DECLARE @SQL varchar(max)
SET @SQL = SELECT LIST FROM MyTable WHERE ColumnID =1
sp_executesql @SQL
You can also pass additional parameter to the procedure that can be used in the executed SQL, see MSDN for more details
Obviously you are open to SQL Injection attacks using this.
Use with caution, especially when you have delete/update statements in there.
Yes, its possible. JonLee has given you a good example of how.
On the point of "Is it recommended?". That's a whole different scenario. You need to be very careful who has access to the table that you use to store your SQL as your stored procedure will simply execute the query it is told to. That will be great while the SQL runs to update your date as you need it but if some joker adds a row that is simply "DROP TABLE XYZ" then your stored proc will run that without question and all your data is gone without you knowing how, where or why.
These scripts will be run with the permission of the account running SQL Agent so this may also allow people to submit SQL that they couldnt run on their own privilege that will complete successfully - eg "UPDATE HRDatabase.dbo.SALARIES SET HourlyRate = HourlyRate * 1.1 WHERE StaffName = 'MyName'"
use this method with a great deal of caution and, in my opinion, only if you cant see an alternative way of achieving the same effect.
