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

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.