CODEWITHSUNDEEP
sql-server-2008
AntonE
AntonE

Reputation: 682

Strange error message: "Must declare the scalar variable" when pass TVP parameters into table-valued function

I have a simple custom type for use it as table valued parameter. Like this:

 CREATE TYPE dbo.PeriodsList AS TABLE
    (
        PeriodDate NVARCHAR(8) NOT NULL
    )

I also have very simple table valued function:

alter FUNCTION GetPeriodsInYear (@periods  dbo.PeriodsList READONLY) returns @PeriodsSet Table(period NVARCHAR(8))

BEGIN
insert @PeriodsSet
select 
    '0' as period 
Return
end

But when I try to execute this function in this way

DECLARE @periods1 dbo.PeriodsList
INSERT INTO @periods1
VALUES ('20130916')

select * from GetPurchasesInYear(@periods1)

I receive error message - "Must declare the scalar variable "@periods1". I have found many examples about stored procedures but not about functions. Is it possible to pass table valued parameters into functions? And where the mistake in my code?

Upvotes: 1

Views: 1596

Answers (1)

Jon Schneider
Jon Schneider

Reputation: 26973

Check the "compatibility level" of your SQL Server 2008 database. If it's set to 80 (SQL Server 2000), you will get the error you are describing when you try to call a function (but not a stored procedure) that takes a table-valued parameter.

It should work if the compatibility level value is 90 (SQL Server 2005) or higher.

To view the compatibility level of your database, do:

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

Source: http://msgroups.net/microsoft.public.sqlserver.programming/pass-table-valued-para/97879

Changing the compatibility level (SQL Server 2008): http://technet.microsoft.com/en-us/library/bb510680%28v=sql.100%29.aspx

Upvotes: 1

Related Questions