How do I execute sql text passed as an sp parameter?

Question

I have a stored procedure with an nvarchar parameter. I expect callers to supply the text for a sql command when using this SP.

How do I execute the supplied sql command from within the SP?

Is this even possible?-

I thought it was possible using EXEC but the following:

EXEC @script

errors indicating it can't find a stored procedure by the given name. Since it's a script this is obviously accurate, but leads me to think it's not working as expected.

Answer 1

you can just exec @sqlStatement from within your sp. Though, its not the best thing to do because it opens you up to sql injection. You can see an example here

Answer 2

You use EXECUTE passing it the command as a string. Note this could open your system up to serious vulnerabilities given that it is difficult to verify the non-maliciousness of the SQL statements you are blindly executing.

Answer 3

Use:

BEGIN

  EXEC sp_executesql @nvarchar_parameter

END

...assuming the parameter is an entire SQL query. If not:

DECLARE @SQL NVARCHAR(4000)
SET @SQL = 'SELECT ...' + @nvarchar_parameter

BEGIN

  EXEC sp_executesql @SQL

END

Be aware of SQL Injection attacks, and I highly recommend reading The curse and blessing of Dynamic SQL.

Answer 4

How do I execute the supplied sql command from within the SP?

Very carefully. That code could do anything, including add or delete records, or even whole tables or databases.

To be safe about this, you need to create a separate user account that only has dbreader permissions on just a small set of allowed tables/views and use the EXECUTE AS command to limit the context to that user.