CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008-r2sql-server-2012
hofnarwillie
hofnarwillie

Reputation: 3660

SQL: Must declare scalar variable

When I run the following T-SQL in SQL Server 2012 it works fine, but in SQL Server 2008 R2 I get the error

Must declare the scalar variable "@in"andMust declare the scalar variable "@out"

Code:

DECLARE @outIds nvarchar(max);
DECLARE @in TimeLineReportList;
DECLARE @out TimeLineReportList;
DECLARE @startDate datetime;
DECLARE @endDate datetime;
DECLARE @assessmentId int;

SET @startDate ='2013-01-01T00:00:00.000'
SET @endDate ='2013-01-01T00:00:00.000'
SET @assessmentId = 14
SET @outIds ='3,9,10'

INSERT INTO @in
   SELECT * 
   FROM dbo.udf_First_Timeline_Entries_Of_Status(@assessmentId)

INSERT INTO @out
   SELECT * 
   FROM dbo.udf_First_Timeline_Entries_Of_Any_Statuses(@outIds)

SELECT * 
FROM dbo.udf_Generic_Timelines(@startDate, @endDate, @in, @out,default)

TimeLineReportList is a user-defined table type that definitely exists in the DB

EDIT I've just tested the first two queries by just running the following and both return results:

SELECT * FROM dbo.udf_First_Timeline_Entries_Of_Status(@assessmentId)
SELECT * FROM dbo.udf_First_Timeline_Entries_Of_Any_Statuses(@outIds)

Upvotes: 2

Views: 3108

Answers (1)

ughai
ughai

Reputation: 9890

As detailed by comment from Aaron Bertrand, The issue is due to the compatibility mode of the database. Changing the compatibility to 90 (SQL Server 2005) fixed the issue. Newer features like UDT are not supported in databases with compatibility mode 80 (SQL Server 2000).

For more details on the effects and behavior of compatibility mode, refer to this thread here.

Upvotes: 1

Related Questions