对没有循环的结果集中的所有行执行动态sql

I have a query that generates a query for each row in a table.

我有一个查询，为表中的每一行生成一个查询。

For example:

例如：

select ' create proc ['+[ProcName]+'] as 
       print '''+[ProcName]+''''
from MyTable

The results of this query will give me a sql statement I can execute for every row of data in the table.

这个查询的结果将给我一个sql语句，我可以为表中的每一行数据执行。

    CREATE PROC [proc_1]
AS
    PRINT 'proc_1'

CREATE PROC [proc_2]
AS
    PRINT 'proc_2'

etc.

等等

Is it possible to execute every row in my result set without having to implement some form of cursor/loop?

是否可以执行结果集中的每一行而不必实现某种形式的游标/循环？

2 个解决方案

#1

You can concatenate all column values in sql pass variable by many ways

您可以通过多种方式连接sql pass变量中的所有列值

as examples: XMLPATH, STUFF or COALESCE, with some manipulation with string.

例如：XMLPATH，STUFF或COALESCE，带有一些字符串操作。

but still getting an error

但仍然出错

The Main Issue for This task is Go

此任务的主要问题是Go

Go is Not-Valid T-SQL

Go是无效的T-SQL

so if you tried execute dynamic sql contains Go, the next error will be raised:-

所以如果你尝试执行动态sql包含Go，则会引发下一个错误： -

Msg 102, Level 15, State 1, Line 4 Incorrect syntax near 'go'.

消息102，级别15，状态1，行4'go'附近的语法不正确。

After surfing the * , I get the resolved here:-

浏览*之后，我在这里得到解决： -

Execute Dynamic Query with go in sql

使用go in sql执行动态查询

so Get the next demo (after applying the above link with my trials):-

所以获取下一个演示（在我的试验中应用上述链接后）： -

Demo:-

演示： -

-- Try to create 4 procedures proc_1, proc_2 , proc_3 and proc_4
Create database Demo
go
use Demo
go

Create table MyTable (procName varchar (200))
go
insert into MyTable values ('proc_1')
go
insert into MyTable values ('proc_2')
go
insert into MyTable values ('proc_3')
go
insert into MyTable values ('proc_4')
go

declare @Query nvarchar(max)
SELECT @Query = isnull(@Query,'') + 'create proc ['+[ProcName]+'] as 
print '''+[ProcName]+''''+ char (10) + '
Go
'
FROM MyTable 
--print @Query
SET @Query = 'EXEC (''' + REPLACE(REPLACE(@Query, '''', ''''''), 'GO', '''); EXEC(''') + ''');'
EXEC (@Query)

Result:-

结果：-

#2

you can declare a variable, store the queries (seperates) inside it and execute it

你可以声明一个变量，将查询（单独）存储在其中并执行它

DECLARE @strQuery Varchar(MAX)

SET @strQuery  = ''

select @strQuery = @strQuery + 
       'EXEC('' create proc [' + [ProcName] + '] 
         as 
         print ''''' + [ProcName] + '''''
         '')'

from MyTable

EXEC(@strQuery)

--To view your query

PRINT(@strQuery)

Note: i used Exec command for each procedure because they cannot be executed at the same time in a query

注意：我为每个过程使用了Exec命令，因为它们不能在查询中同时执行

#1