How to execute sql scripts from a staging table in sql stored procedure

Question

What are the best approaches to the below scenario?

In my C# program, I have some sql commands such as UPDATE, INSERT, DELETE. I could execute them in my C# program (which works fine) but I need to execute these sql commands when one of the stored procedure comes to the last line. So I am planning to store the sql commands in some staging table in the same database and then I would like to open this table within the stored procedure and execute one by one.

What is the best approach in terms of opening a table within stored procedure and then traversing through the table based on some condition (like select * from TempStagingTable where customerId = '1000'). If it returns 10 records, I would like to loop them and execute the sql command stored in a column named "CustomSqlScript"

PS: I am using SQL 2008 R2.

Answer 1

Well, you can select the data from table A initially, and instead of using cursor you can use while loop(as it will increase your performance compared to cursor) and then you can execute your predefined SQL statement from table B

Please find below sql scripts to do the same

Please note: I have not made use of any relation,This is just a plain example

CREATE TABLE test1(
  customSqlScripts VARCHAR(100)
)

CREATE TABLE test2(
 customer_Id INT PRIMARY KEY ,
 first_Name VARCHAR(100),
 last_name  VARCHAR(100)
)

 INSERT INTO test1 VALUES('Select first_Name from test2 where customer_Id=')
 INSERT INTO test2 VALUES('1','Rohit','Tiwari')

 DECLARE @Count INT
 DECLARE @iCount INT=0
 DECLARE @dummysql VARCHAR(100)

 SELECT @Count= Count(*) 
 FROM test2 
 WHERE last_name='Tiwari'

 WHILE(@icount<@count)
 BEGIN
  SELECT @dummysql =customSqlScripts 
  FROM test1
  SET @dummysql=@dummysql+'1'
  EXEC (@dummysql)
  SET @icount=@icount+1
 END