如何从变量运行生成的SQL？(How to run generated SQL from a variable?)

如何从变量运行生成的SQL？(How to run generated SQL from a variable?)

我试着运行我的SQL（在T-SQL中）（我只是将其处理成一个变量），但是我无法运行它。 我想要做的是：1.运行程序2中的大SQL。Big SQL生成selct-SQL 3.运行生成的sql，就像正常选择和接收数据一样。 我认为这可以用sp_executesql完成，但它看起来不适合我的情况。

我试图看起来像这样：

declare @sql varchar(max) set @sql = 'select x, y from z' exec @sql --this is the point where im stuck.

我知道这一定是一个很基本的问题，但我找不到适合我的问题的东西。

谢谢你的帮助！

更新我通过使用sp_sqlexec（不再支持，但像我想要的那样工作）解决了我的问题。

declare @sql varchar(max) set @sql = 'select x, y from z' exec sp_sqlexec @sql

正确的解决方案是sp_executesql！ （请参阅sp_sqlexec与sp_executesql ）对于我的问题，如果我“重新构建 ”可以使用它的所有内容，这将非常耗时。

谢谢你们的帮助！

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!

最满意答案

你需要括号exec (@sql)

SQL Server会在@sql变量中Could not find stored procedure 'select x, y from z'.名称的存储过程，而Could not find stored procedure 'select x, y from z'.这种情况，并且抱怨Could not find stored procedure 'select x, y from z'.

如果您正在使用动态SQL，请参阅动态SQL 的诅咒和祝福，以获取有关该主题的好文章。

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.