SQL Error: “Must declare the scalar variable” when passing a table parameter to a table-valued function

Question

The following simple SQL example is returning an error.

Here's a table type that's passed to a table-valued function:

CREATE TYPE Ids
    AS TABLE
    (
        Id int NOT NULL,
        PRIMARY KEY( Id )
    );
GO

And here is the table-valued function that fails:

CREATE FUNCTION GetIds
(
    @ids -- or null
        Ids READONLY
)
RETURNS
    @result
        TABLE
        (
            EachId int
        )
AS
BEGIN
    IF @ids IS NOT NULL
        INSERT INTO @result
            SELECT Id
            FROM @ids;
    RETURN;
END;
GO

The error returned is:

Msg 137, Level 16, State 1, Procedure GetIds, Line 28
Must declare the scalar variable "@ids".

I've read posts that say that it happens when the SQL compatibility level is too old, but the following returns 100:

SELECT compatibility_level 
FROM sys.databases 
WHERE name = 'TheDatabaseName';

Any suggestions would be greatly appreciated.

The error is because you use variables that are not declared. Example declare @ids table(Ids int) — S.Visser
S.Visser: it is declared, as being of type Ids. This is how you pass a table as a parameter. — uncaged
Its created, not declared. If you refer to it you need to declare it. Think this will msdn doc will explain msdn.microsoft.com/en-us/library/bb510489.aspx — S.Visser
I think you're miss-reading that example. Note that @TVP is passed as the parameter, and nowhere is "declared". — uncaged
Oddly, removing the line "IF @ids IS NOT NULL" and the error goes away. It seems I'll need to check for an empty table instead. — uncaged

Mike Clark Mike Clark · Accepted Answer · 2015-05-20T03:51:05

Let me tell you table type parameter is just like a data table.

So, if you want to put if condition on it then,

just change your if condition of function as below:

IF (select count(*) from @ids) > 0

Complete function code is:

CREATE FUNCTION GetIds
(
@ids Ids READONLY      
)
RETURNS @result TABLE(EachId int)
AS
BEGIN
IF (select count(*) from @ids) > 0
    INSERT INTO @result
    SELECT Id FROM @ids;            
RETURN;
END;

SQL Error: “Must declare the scalar variable” when passing a table parameter to a table-valued function

2 Answers