Revision history [back]

One approach to executing a stored procedure in a loop for every record within a user-defined table type parameter is to use a cursor. Here is an example of how this can be done:

1. Define the user-defined table type:

CREATE TYPE MyTableType AS TABLE (
   ID int,
   Name varchar(50)
);

1. Define the stored procedure that takes the user-defined table type parameter:

CREATE PROCEDURE MyProcedure
   @TableType MyTableType READONLY
AS
BEGIN
   DECLARE @ID int
   DECLARE @Name varchar(50)

   DECLARE cur CURSOR LOCAL FOR
      SELECT ID, Name
      FROM @TableType

   OPEN cur

   FETCH NEXT FROM cur INTO @ID, @Name

   WHILE @@FETCH_STATUS = 0
   BEGIN
      -- Execute the stored procedure for each record in the table type
      EXEC MyOtherProcedure @ID, @Name

      FETCH NEXT FROM cur INTO @ID, @Name
   END

   CLOSE cur
   DEALLOCATE cur
END

1. Define the stored procedure that will be executed for each record in the table type:

CREATE PROCEDURE MyOtherProcedure
   @ID int,
   @Name varchar(50)
AS
BEGIN
   -- Do something with the ID and Name values
   PRINT @ID
   PRINT @Name
END

1. Call the main stored procedure with a table variable that contains the data:

DECLARE @MyTable MyTableType

INSERT INTO @MyTable (ID, Name)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Bob')

EXEC MyProcedure @MyTable

This will execute the MyOtherProcedure stored procedure for each record in the @MyTable variable.