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
)
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.