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:
CREATE TYPE MyTableType AS TABLE (
ID int,
Name varchar(50)
);
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
CREATE PROCEDURE MyOtherProcedure
@ID int,
@Name varchar(50)
AS
BEGIN
-- Do something with the ID and Name values
PRINT @ID
PRINT @Name
END
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.
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2023-07-08 05:46:39 +0000
Seen: 8 times
Last updated: Jul 08 '23
Can the names of rows be modified in MUI DataGrid?
How to modify the name of existing captions in MS Word?
How can you use linq to choose a specific column from a datatable?
What is the meaning of the role being returned as 'undefined' in DiscordJS?
What is the process of obtaining a column name by using a given row and its corresponding value?
What is the process for changing the name of a dropdown in a Grafana table query?