Run dynamic sql for all rows in the result set without loop

Question

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

For instance:

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

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

    CREATE PROC [proc_1]
AS
    PRINT 'proc_1'

-

CREATE PROC [proc_2]
AS
    PRINT 'proc_2'

and etc.

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

Answer 1

You can combine all the column values ​​in the sql pass variable in many ways.

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

Go

Go T-SQL

, sql Go, : -

Msg 102, Level 15, State 1, Line 4 "go".

stackoverflow, : -

sql

( ): -

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)

: -

Answer 2

, ()

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)

: Exec ,