How to run generated SQL from a variable?

Question

I've tried running my SQL (in T-SQL) (I just genereated into a variable) but I can't get it to run. What I want to do is: 1. Run big SQL from Program 2. Big SQL generates selct-SQL 3. run generated sql like normal select and receive data like normal. I thought it could be done done with sp_executesql but it looks like it's not right in my case.

What I'm trying looks like this:

declare @sql varchar(max)

set @sql = 'select x, y from z'

exec @sql --this is the point where im stuck.

I know this must be quite a basic question, but I couldn't find something that fits to my problem on google.

Thanks for your help!

Update I solved my problem by using sp_sqlexec (which isn't supported anymore but works like I wanted).

declare @sql varchar(max)

set @sql = 'select x, y from z'

exec sp_sqlexec @sql 

The correct solution is sp_executesql! (see sp_sqlexec vs. sp_executesql) For my problem it would be quite time consuming if i would "rebuild" everything that I could use it.

Thanks for your help guys!

Solution 1

You need parentheses exec (@sql)

SQL Server will look for a stored procedure of the name in the @sql variable without this and complain Could not find stored procedure 'select x, y from z'.

If you are using dynamic SQL See The Curse and Blessings of Dynamic SQL for a good article on the topic.

Solution 2

You can also use sp_executesql, but note that it needs NVARCHAR (Unicode)

Also, if you are building dynamic filters, you can pass in parameters as per below

declare @SQL nvarchar(max)
set @SQL = N'select x, y from z where x = @someFilter'
exec sp_executesql @SQL, N'@someFilter bigint', @someFilter = 6034280