Click here to Skip to main content
15,889,867 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want To Create Multiple User defined Type Table in a Stored Procedure like below code


CREATE PROCEDURE [dbo].[Add_Program]
@id INT OUTPUT,
@name VARCHAR(30),
@status CHAR(1),
@modifiedBy INT ,

@tbl_Program_Product_Price dbo.Type_Program_Product_Price READONLY
@tbl_Program_Product_Type dbo.Type_Program_Product_Type READONLY
Posted

1 solution

The following describes how to create User defined table type and also how to use them in a SP

SQL
CREATE PROCEDURE CreatingUserDefiendTableType
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TYPE TableType1 AS TABLE
        ( Name VARCHAR(50)
            , Code INT );

CREATE TYPE TableType2 AS TABLE
        ( Name VARCHAR(50)
            , ID INT );


END
GO


Call the SP
exec CreatingUserDefiendTableType


Create another SP for using them

SQL
Create PROCEDURE [dbo].UsingUserDefiendTableType
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    declare @type1 TableType1

    declare @type2 TableType2

    insert into @type1 values ('TestTableType1' , 12);

    Select * from @type1

    insert into @type2 values ('TestTableType2' , 13);

    Select * from @type2


END



Call the Second SP
exec UsingUserDefiendTableType
 
Share this answer
 
Comments
Rajaguru Jeevanantham 9-Jul-14 1:31am    
thanks

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900