Execution of Stored Procedure for every row of table using string variable

Question

I want to execute a stored procedure for every row of a table without using cursor or loop. Lets say my table dbo.User contains name for following users:

create table dbo.test_User  ( 
 Name varchar(50) )

insert into dbo.test_User  
   values  ('Deepanshu'),('IronMan'),('DoctorStrange')

I created a stored procedure which will only display the name of users (Although my real stored procedure has to do a lot of stuff)

create procedure dbo.usp_TestSP ( @name varchar(50) )
as
BEGIN
    select @name
END

Now i want the stored procedure to run for all names like:

EXEC dbo.usp_TestSP 'Deepanshu';
EXEC dbo.usp_TestSP 'IronMan';
EXEC dbo.usp_TestSP 'DoctorStrange';

I created a string variable @Query which will store the t-sql query i want to execute

DECLARE @Query varchar(200);  
select @Query=STUFF(  
                    (select 'dbo.usp_TestSP '''+Name+''';' 
                     from dbo.test_user  
                     FOR XML PATH('')
                    ),1,0,''
                 )  
EXEC @Query

When i try to execute this @Query it gives me an error saying:

Could not find server 'dbo' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

How can i execute the stored procedure for all the names using the string variable @Query?

Possible duplicate of How do I execute a stored procedure once for each row returned by query? — Igor
– Igor, Commented May 25, 2018 at 9:51
Thanks for answering but as i have mentioned, i want to execute this without cursor or loop, using the same logic as displayed above — Deepanshu Tyagi
– Deepanshu Tyagi, Commented May 25, 2018 at 9:51
You are just missing the EXEC command in your literal. EXEC dbo.usp_TestSP ... — EzLo
– EzLo, Commented May 25, 2018 at 10:08

Lukasz Szozda · Accepted Answer · 2018-05-25 17:37:29Z

3

You could use CURSOR to simulate FOR-EACH with stored procedure call:

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR 
SELECT *FROM  dbo.tesT_User;

DECLARE @name NVARCHAR(50);

OPEN db_cursor;
FETCH NEXT FROM db_cursor INTO @name;

WHILE @@FETCH_STATUS = 0  
BEGIN  
      EXEC dbo.usp_TestSP @name = @name;

      FETCH NEXT FROM db_cursor INTO @name;
END 

CLOSE db_cursor;
DEALLOCATE db_cursor;

DBFiddle Demo

Warning:

If it would be simple function then CROSS/OUTER APPLY is a way to go.

SELECT *
FROM dbo.Test_User tu
OUTER APPLY (SELECT dbo.usp_TestSP(tu.name)) s(a);

DBFiddle Demo2

EDIT

i want to execute this without cursor or loop, using the same logic as displayed above

DECLARE @x NVARCHAR(MAX) = 
   (SELECT string_agg(FORMATMESSAGE('EXEC dbo.usp_TestSP @name=''%s'';', name)
   ,CHAR(13)) AS r FROM dbo.test_User);
PRINT @x;
EXEC(@x);

DBFiddle Demo3

And finally your code:

DECLARE @Query varchar(MAX);  
select @Query=STUFF((SELECT 'EXEC dbo.usp_TestSP @name=' +QUOTENAME(Name,'''') 
       + ';' from dbo.test_user
FOR XML PATH('')),1,0,'');
EXEC(@Query);

DBFiddle Demo4

What I did:

using semicolons (;) it's very good practice
changed datatype to VARCHAR(MAX)
added EXEC inside SQL
wrapped @Query with ()

edited May 25, 2018 at 17:37

answered May 25, 2018 at 9:46

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan Guzman Over a year ago

To handle embedded quotes, I suggest (select 'EXEC dbo.usp_TestSP ' +QUOTENAME(Name,'''') + ';' from dbo.test_user

Collectives™ on Stack Overflow

Execution of Stored Procedure for every row of table using string variable

1 Answer 1

EDIT

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related