Execute dynamic sql for all rows in a result set without a loop

Question

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.

    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?

Answer 1

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

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

but still getting an error

The Main Issue for This task is Go

Go is Not-Valid T-SQL

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

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

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

Execute Dynamic Query with 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:-

Answer 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