如何在存储过程中使用多个用户定义的类型表 [英] how to use multiple User defined Type Table in a Stored 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

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

推荐答案

下面介绍如何创建用户定义的表格类型以及如何在SP中使用它们

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

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

拨打SP

Call the SP

exec CreatingUserDefiendTableType

创建另一个使用它们的SP

Create another SP for using them

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

拨打第二个SP

Call the Second SP

exec UsingUserDefiendTableType

这篇关于如何在存储过程中使用多个用户定义的类型表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！