检查 UTD 参数在存储过程中是否有值 [英] Checking if UTD parameter has values in stored procedure

问题描述

我想检查具有某些用户定义表类型的参数是否有值或是否为 NULL，但我收到以下错误

I want to check if a parameter with some User-Defined Table Type has values or is NULL but i'm receiving the following error

Msg 137, Level 16, State 1, Procedure SearchByWord, Line 63 [Batch Start Line 7]
Must declare the scalar variable "@Words".

存储过程如下(仅相关部分)

The stored procedure is as follow (only the relevant part)

CREATE PROCEDURE [dbo].[SearchByWord]
(
    @Words Word_List READONLY
)
AS
BEGIN

    SET NOCOUNT ON;

    -- some DECLARE and SET

    IF @Words IS NOT NULL
    BEGIN
       -- stuff
    END

    -- more stuff
END

用户定义的表类型是

CREATE TYPE [dbo].[Word_List] AS TABLE(
    [element] [varchar](512) NULL
)

推荐答案

使用

IF EXISTS(SELECT * FROM @Words)

不是

IF @Words IS NOT NULL

表值参数将始终存在，并且不能像标量参数那样 NULL.

The table valued parameter will always be present and can't be NULL like a scalar parameter.

如果你调用 exec [dbo].[SearchByWord] 而不传递任何参数，结果是 @Words 将是空表.

If you call exec [dbo].[SearchByWord] without passing anything for the parameter the result is that @Words will be empty table.

这篇关于检查 UTD 参数在存储过程中是否有值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！