Search code examples
sql-serverstored-proceduresuser-defined-types

Checking if UTD parameter has values in stored procedure


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

The User-Defined Table type is

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

Solution

  • Use

    IF EXISTS(SELECT * FROM @Words)
    

    Not

    IF @Words IS NOT NULL
    

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

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