CODEWITHSUNDEEP
sqlsql-server
uncaged
uncaged

Reputation: 797

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

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.

Upvotes: 3

Views: 4004

Answers (2)

Ali pishkari
Ali pishkari

Reputation: 542

just check if you have any record in you table 

if(select count(1) from @ids)>0

Upvotes: 0

Mike Clark
Mike Clark

Reputation: 1870

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;

Upvotes: 1

Related Questions