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.

I agree with all the security comments, but you can try [ EXEC (@script) ] which should work. — Sparky, Feb 18 '10 at 05:45
@Sparky: While valid, `EXEC` won't cache the query plan while `EXEC sp_executesql` will: http://www.sommarskog.se/dynamic_sql.html — OMG Ponies, Feb 18 '10 at 15:33
True, but if the users are going to supply text for SQL (which scares me, but), I would expect the query plans to vary. Now, if we can only convince people NOT to let users do this... — Sparky, Feb 18 '10 at 16:09

score 8 · Accepted Answer · answered Feb 18 '10 at 02:24

8

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.

answered Feb 18 '10 at 02:24

OMG Ponies

325,700
82
523
502

sp_executesql made the difference. Looks like the article will explain why. Thanks. – Dane O'Connor Feb 18 '10 at 02:30
1

@TheDeeno sp_executesql is **not** enough. It will still allow UPDATE, CREATE, and DROP statements to go through. You need to limit it to a user that only has dbreader – Joel Coehoorn Feb 18 '10 at 02:39
What's the reason for wrapping the EXEC sp_executesql call in a BEGIN/END block? – Daniel Rehner Oct 24 '11 at 20:08

score 0 · Answer 2 · edited May 23 '17 at 12:16

0

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

edited May 23 '17 at 12:16

Community

1
1

answered Feb 18 '10 at 02:23

Josh

590
3
4

While valid, `EXEC` won't cache the query plan while `EXEC sp_executesql` will: http://www.sommarskog.se/dynamic_sql.html – OMG Ponies Feb 18 '10 at 02:25
Ah, I edited to slow. I'm fine with injection risk since this will be used by admins only. I must be doing something wrong. Any thoughts? – Dane O'Connor Feb 18 '10 at 02:25

score 0 · Answer 3 · answered Feb 18 '10 at 02:24

0

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.

answered Feb 18 '10 at 02:24

AaronLS

37,329
20
143
202

Joel Coehoorn · Answer 4 · 2010-02-18T02:38:30.490

0

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.

edited Feb 18 '10 at 02:38

answered Feb 18 '10 at 02:25

Joel Coehoorn

399,467
113
570
794

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

4 Answers4

Linked