SQL error: "Must declare scalar variable" when passing table parameter to table function
The following simple SQL example returns an error.
Here is the type of table that is passed to the table function:
CREATE TYPE Ids
AS TABLE
(
Id int NOT NULL,
PRIMARY KEY( Id )
);
GO
And here's this table-valued function 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
Returned error:
Msg 137, Level 16, State 1, Procedure GetIds, Line 28
Must declare the scalar variable "@ids".
I've read posts that say this 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 are greatly appreciated.
+3
source to share
2 answers
Let me say that the table type parameter is like a data table.
So, if you want to put a condition on it, then
just change the if condition like below:
IF (select count(*) from @ids) > 0
Full function code:
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;
+1
source to share