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?

How many proc's do you want to create in this manner? If it's so many it's going to be a problem to loop, it's maybe not a good idea... — HoneyBadger, Feb 14 '17 at 12:20
@HoneyBadger probably only about 20 or so, it's more to satisfy my own curiosity if it's possible :) — Neil P, Feb 14 '17 at 12:22

score 2 · Accepted Answer · edited May 23 '17 at 12:26

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:-

Great solution, this also gets around the potential problem of having two variables with the same name, in the same batch. — Neil P, Feb 14 '17 at 14:04

Yahfoufi · Answer 2 · 2017-02-14T13:33:36.963

1

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

edited Feb 14 '17 at 13:33

answered Feb 14 '17 at 12:29

Yahfoufi

2,220
1
22
41

raising an error : `Msg 156, Level 15, State 1, Procedure proc_1, Line 2 Incorrect syntax near the keyword 'proc'. Msg 156, Level 15, State 1, Procedure proc_1, Line 3 Incorrect syntax near the keyword 'proc'. Msg 156, Level 15, State 1, Procedure proc_1, Line 4 Incorrect syntax near the keyword 'proc'.` – ahmed abdelqader Feb 14 '17 at 12:39
@ahmedabdelqader i provided the way to solve the issue, the syntax error is from the query. that i copied from the OP example. or maybe it is because i didn't add ligne feed – Yahfoufi Feb 14 '17 at 12:50
2

NOP, it is related to your code, the main issue about `Go` , `Go` should be on the separated line, plz execute your code before posting it as an answer. – ahmed abdelqader Feb 14 '17 at 12:54
@ahmedabdelqader i agree with you. i edited my answer. thx for the remark – Yahfoufi Feb 14 '17 at 13:34
@ahmedabdelqader , i used `exec` inside the dynamic query because it doesn;t work even when using `GO` – Yahfoufi Feb 14 '17 at 13:39
Now it is working , but what about the performance, execute the all query via one exec or each proc has its own exec , You said ` they cannot be executed at the same time in a query`, this is not correct , plz follow my answer for getting the solution. – ahmed abdelqader Feb 14 '17 at 13:40

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

2 Answers2