How do I execute sql text passed as an sp paramete

问题:

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.

回答1:

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.

回答2:

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

回答3:

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.

回答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.