loop through table records and execute Stored Proc with row data as params in sql server

Question

Hi,
I import data from csv file into a temp table. I need to loop through each row and call Stored procedure and pass that row details to the stored procedure as parameters. I know that it can be done using cursor and i have implemented it. I would like to know if there are any latest best methods to do it.

table
loop through each row and get values into variables
exec sp param1, param2
next

something like this. The file may have around 900 records.
Please point me to right resource or code.

Thanks,

Answer 1

Hi @Spunny ,

Please try the following solution.
It will work starting from SQL Server 2012 onwards.

SQL

DECLARE @IDVar INT, @NameVar VARCHAR(50);  
  
DECLARE @tbl TABLE (ID INT, [Name] VARCHAR(50));  
INSERT @tbl (ID, [Name])  
VALUES (1, 'Employee')  
   , (2, 'Department')  
   , (3, 'Class');  
  
DECLARE @RowCount INT = (SELECT COUNT(*) FROM @tbl);  
  
WHILE @RowCount > 0 BEGIN  
 SELECT @IDVar=ID, @NameVar=[Name]   
 FROM @tbl   
 ORDER BY ID DESC OFFSET @RowCount - 1 ROWS FETCH NEXT 1 ROWS ONLY;  
     
 -- do whatever needed, apply any logic, call stored procedures, etc.  
 /*  
 Exec usp_Employee @name = @NameVar  
 Exec usp_Department @ID = @IDVar,@name = @NameVar  
 */  
  
   SET @RowCount -= 1;  
END  

Answer 2

The cursor is the best option in my opinion. Here is the best pattern to run a cursor:

DECLARE @cur CURSOR
SET @cur = CURSOR STATIC FOR
    SELECT ...

OPEN @cur

WHILE 1 = 1
BEGIN
     FETCH @cur INTO @var1, @var2, ...
     IF @@fetch_status <> 0
        BREAK

    -- Do stuff here.

END

Key points:

• Cursor variables means that typos are caught at compile time.
• STATIC cursor means that result is copied to a worktable in temp table and served from that table. Default is DYNAMIC which means that cursor is evaluated on every row.
• Note the structure of the loop itself. There is only one FETCH to improve maintainability.

Answer 3

Hi，@Spunny

Welcome to Microsoft T-SQL Q&A Forum!

when you do not like use Cursor,Maybe while is your best choice,please try this:

DECLARE @RowCnt int; SET @RowCnt = 0 -- Loop Counter  
  
-- Use a table variable to hold numbered rows containg MyTable's ID values  
DECLARE @tblLoop TABLE (RowNum int IDENTITY (1, 1) Primary key NOT NULL,  
     ID INT )  
INSERT INTO @tblLoop (ID)  SELECT ID FROM MyTable  
  
  -- Vars to use within the loop  
  DECLARE @Code NVarChar(10); DECLARE @Name NVarChar(100);  
  
WHILE @RowCnt < (SELECT COUNT(RowNum) FROM @tblLoop)  
BEGIN  
    SET @RowCnt = @RowCnt + 1  
    -- Do what you want here with the data stored in tblLoop for the given RowNum  
    SELECT @Code=Code, @Name=LongName  
      FROM MyTable INNER JOIN @tblLoop tL on MyTable.ID=tL.ID  
      WHERE tl.RowNum=@RowCnt  
    PRINT Convert(NVarChar(10),@RowCnt) +' '+ @Code +' '+ @Name  
END  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.