How to execute sql text passed as sp parameter?

Question

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

How to execute the provided sql command from SP?

Is it possible? -

I thought this was possible using EXEC, but the following:

EXEC @script 

indicating that it cannot find the stored procedure by the given name. Since this is a script, it is obviously accurate, but it makes me think that it does not work as expected.

Answer 1

Using:

 BEGIN EXEC sp_executesql @nvarchar_parameter END 

... provided that the parameter is a full SQL query. If not:

 DECLARE @SQL NVARCHAR(4000) SET @SQL = 'SELECT ...' + @nvarchar_parameter BEGIN EXEC sp_executesql @SQL END 

Remember the SQL Injection attacks , and I highly recommend reading the curse and blessing of Dynamic SQL .

Answer 2

you can just execute the @sqlStatement command from your sp. Although, this is not the best thing to do, because it opens you up to SQL injection. You can see an example here.

Answer 3

You use EXECUTE , passing it a command as a string. Please note that this can open your system to serious vulnerabilities, given that it is difficult to verify the unnamed SQL statements that you blindly execute.

Answer 4

How to execute the provided sql command from SP?

Very careful. This code could do anything, including adding or removing records, or even entire tables or databases.

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