CODEWITHSUNDEEP
sqlstored-proceduresuser-defined-types
Ak C
Ak C

Reputation: 53

How to loop User Defined Data Types in SQL and pass parameters to Procedure

Consider I have below User Defined Data Type:

CREATE Type udt_MyType as Table(
@ID int,
@Name varchar(20),
@Address varchar(100)
)

Consider I have stored Procedure as below:

CREATE PROCEDURE sp_MyProc (
 @ID int,
 @Name varchar(20),
 @Address varchar(100)
)
----Some Execution----

Now I want to create new Procdure which call above sp_MyProc by looping on udt_MyType (***But without doing any changes to sp_MyProc) So I want something like below:

CREATE Procedure sp_NewProc(
@udt as udt_MyType READONLY
)
AS
BEGIN
     WHILE LOOP on @udt   ----to get each row
     BEGIN
     exec sp_MyProc @udt.ID,@udt.Name,@udt.Address
     NEXT
     END
END

Note : I dont have permission to change sp_MyProc
Is it possible to achieve sp_NewProc? I want to loop on UDT and pass parameters to procedure?

Upvotes: 0

Views: 1074

Answers (1)

Rajeev Kumar
Rajeev Kumar

Reputation: 371

Use cursor instead of a while loop.

CREATE Procedure sp_NewProc(
@udt as udt_MyType READONLY
)
AS
BEGIN
    DECLARE @ID int,
    @Name varchar(20),
    @Address varchar(100)
    
    Declare cursor_A cursor
    For Select * from @udt
    
    Begin
        OPEN cursor_A
    
        FETCH NEXT FROM cursor_A INTO @ID, @Name, @Address;
            WHILE @@FETCH_STATUS = 0
            BEGIN
                -- To DO Logic
                exec sp_MyProc @ID,@Name,@Address

                FETCH NEXT FROM cursor_A INTO @ID, @Name, @Address;
            END;
    
            CLOSE cursor_A;
            DEALLOCATE cursor_A;
    End;
End;

Upvotes: 2

Related Questions