MSSQL中表值参数的使用

参考链接: MSDN

修改表值参数后，所有使用到的地方都需要修改！！！

定义表值参数

if exists (select 1 from systypes where name='TVP_ContainerCodeStock')
begin
    drop TYPE TVP_ContainerCodeStock
end
-- 表值参数 容器库存变化使用
CREATE TYPE TVP_ContainerCodeStock AS TABLE
(
    CKID VARCHAR(30),
    HZID VARCHAR(30),
    CKKW VARCHAR(30),
    ContainerCode VARCHAR(100),
    ContainerType INT,
    ContainerQty INT 
);

创建之后的位置：可编程性->类型->用户定义数据类型

在简单查询中使用

DECLARE @tempContainerStock AS TVP_ContainerCodeStock

INSERT INTO @tempContainerStock(CKID,HZID,CKKW,ContainerCode,ContainerType,ContainerQty)
select CKID,HZID,CKKW,ContainerCode,ContainerType,ContainerQty 
from TAccContainerStock 
where ContainerCode in ('ZQ00000001','ZQ00000002')

-- 查询表值参数的数据
select * from @tempContainerStock

传参给存储过程

DECLARE @tempContainerStock AS TVP_ContainerCodeStock

INSERT INTO @tempContainerStock(CKID,HZID,CKKW,ContainerCode,ContainerType,ContainerQty)
select CKID,HZID,CKKW,ContainerCode,ContainerType,ContainerQty 
from TAccContainerStock 
where ContainerCode in ('ZQ00000001','ZQ00000002')

-- 传给存储过程，并获取存储过程执行结果
declare @execResult table(issuccess int,errmsg nvarchar(255))
insert into @execResult
exec Proc_TAccContainerStockUpdate @tempContainerStock,@BillId,@Operator
if 0=(select issuccess from @execResult) 
begin
    select @issuccess= issuccess,@errmsg=errmsg from @execResult
    goto Err
end

查询哪些存储过程使用过某个表值参数

SELECT p.name AS ProcedureName,m.definition AS ProcedureDefinition  
FROM sys.objects p  
JOIN sys.sql_modules m ON p.object_id = m.object_id  
WHERE 1=1 AND p.type = 'P' -- 存储过程  
AND m.definition LIKE '%TVP_ContainerCodeStock%'; -- 替换为你的表值参数名称

请注意，这个查询是基于文本搜索的，因此可能不是非常精确。如果存储过程的定义很长或结构复杂，这种搜索方法可能会返回一些不相关的结果。此外，如果表值参数的名称在存储过程的定义中被动态构建或以其他方式间接引用，那么这个查询可能无法找到它。

如果你想要一个更精确的结果，可能需要使用更复杂的查询或脚本，结合SQL Server的动态管理视图（DMVs）和函数来进行分析。然而，请注意，这样的查询可能会更加复杂，并且可能需要根据具体的数据库结构和存储过程实现进行定制。

最后，如果你经常需要执行此类查询或分析，可能需要考虑使用专门的数据库工具或编写自定义脚本来帮助你更高效地找到使用特定表值参数的存储过程。

存储过程使用表值参数示例：统一更新容器库存的存储过程

-- 更新容器库存
if exists (select 1
          from sysobjects
          where  id = object_id('Proc_TAccContainerStockUpdate')
          and type in ('P','PC'))
   drop procedure Proc_TAccContainerStockUpdate
go

create procedure Proc_TAccContainerStockUpdate  
    @tempContainerStock TVP_ContainerCodeStock READONLY,      
    @BillId varchar(50),
    @Operator  varchar(30)      
as
BEGIN
    SET NOCOUNT ON  
    DECLARE @TranStarted bit = 0 
            ,@issuccess int  = 0    
            ,@errmsg varchar(255) = ''

    --事务前数据准备，比如把一些数据准备到临时表中

    --偿试开始事务；这很重要，一是防止事务嵌套（代码也会开启事务） 二是尽量减小事务范围  
    IF (@@TRANCOUNT = 0)  
    BEGIN  
        BEGIN TRANSACTION  
        SET @TranStarted = 1  
    END  

    --其他存储过程执行语句
    UPDATE a SET a.ContainerQty = ISNULL(a.ContainerQty,0) + b.ContainerQty,a.ModifyEmp=@Operator,a.ModifyDate=GETDATE()
    FROM dbo.TAccContainerStock a,@tempContainerStock b
    WHERE a.CKID=b.CKID AND a.HZID=b.HZID  AND a.ContainerCode=b.ContainerCode
    IF @@error<>0 GOTO Err

    INSERT INTO dbo.TAccContainerStock(CKID,HZID,CKKW,ContainerCode,ContainerType,ContainerQty,CreateEmp,CreateDate,OrigBillId)
    SELECT CKID,HZID,CKKW,ContainerCode,ContainerType,ContainerQty,@Operator,GETDATE(),@BillId 
    FROM @tempContainerStock b
    WHERE NOT EXISTS(
        SELECT 1 FROM TAccContainerStock a WHERE a.CKID=b.CKID AND a.HZID=b.HZID AND a.ContainerCode=b.ContainerCode
    )
    IF @@error<>0 GOTO Err

    UPDATE a SET a.CKKW=''
    FROM dbo.TAccContainerStock a,@tempContainerStock b
    WHERE a.CKID=b.CKID AND a.HZID=b.HZID AND a.ContainerCode=b.ContainerCode AND a.ContainerQty<=0
    IF @@error<>0 GOTO Err

    ---------------------------------------------   
    Select  @errmsg = '成功！', @issuccess = 1  
    --------------------------------------------    
    Ret:
        If @TranStarted = 1 COMMIT TRAN      
        Select @issuccess as issucess, @errmsg as errmsg    
        RETURN  
    Err:
        If @TranStarted = 1 ROLLBACK TRAN      
        Select @issuccess as issucess, @errmsg as errmsg 
        -- 加这个很重要，在批量执行时能出错中断后续逻辑，并且要加在Select返回的后面，确保平台执行存储过程方法正常接收返回 
        raiserror(@errmsg, 16, 1) -- 抛出一个异常
        RETURN  
end
go