sp_executesql with a string from db

Question

Hi,

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

Thanks

Answer 1

Something like this?

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

Answer 2

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

Answer 3

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)!