CODEWITHSUNDEEP
sql-servert-sqlsyntax
pistacchio
pistacchio

Reputation: 58883

sp_executesql with a string from db

how can I retrieve a string (like 'select 1') from a record and pass it to sp_executesql to make it eval it?

Thanks

Upvotes: 1

Views: 419

Answers (3)

John Sansom
John Sansom

Reputation: 41819

Here you go, using sp_executeSQL

DECLARE @MySQL NVARCHAR(MAX)
SELECT @MySQL = 'select top 1 * from Information_schema.tables'
EXEC sp_executeSQL @MySQL

EDIT: For a great read, that some refer to as the definitive guide on working with dynamic T-SQL refer to the article:

The Curse and Blessings of Dynamic SQL

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147234

Just to add to Robin's answer. Important thing is to make sure you ensure you have protection against malicious SQL in the field - you don't say how the statement gets in to the db, but you need to be very careful not to allow bad SQL to get in there (e.g. don't want your data to be deleted by a rogue statement)!

Upvotes: 1

Robin Day
Robin Day

Reputation: 102478

Something like this?

DECLARE @MySQL nvarchar(MAX)
SELECT @MySql = SqlColumn FROM MyTable WHERE MyId = 1
EXEC(@MySql)

Upvotes: 3

Related Questions