question

Frixum avatar image
Frixum asked

Executing a query which is stored in a table column

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

query
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sp_lock avatar image
sp_lock answered
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.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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.

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
+1 Totally agree on the dangers of using dynamic SQL this way.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.