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

Question

question

Spunny-0938 asked May 12, '22 BertZhoumsft-7490 commented May 16, '22

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

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,

sql-server-transact-sql

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 · May 16 at 09:50 AM

Hi,@Spunny-0938

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Bert Zhou

0 ·

Answer 1 · 2022-05-12T14:01:44.98Z

YitzhakKhabinsky-0887 answered May 12, '22 YitzhakKhabinsky-0887 edited May 12, '22

Hi @Spunny-0938,

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-05-12T20:30:08.437Z

ErlandSommarskog answered May 12, '22

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-05-13T03:09:03.15Z

BertZhoumsft-7490 answered May 13, '22 BertZhoumsft-7490 edited May 13, '22

Hi，@Spunny-0938

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.

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question