while using table valued parameter getting scalar variable error in sp

Question

i created table valued parameter like this:

CREATE TYPE dbo.ss AS TABLE(ss1 NVARCHAR(5));

then i wrote my stored procedure like this:

ALTER PROCEDURE [dbo].[T_TransactionSummary] 
 @locations dbo.ss readonly
as
begin
...............
.............
AND (Location_tbl.Locid IN (@locations))

while executing this i am getting error:

Must declare the scalar variable "@locations".

What is wrong with my stored procedure

Answer 1

Seriously? I showed you here that you can't use IN (@TVP) but instead must use a different technique, e.g. WHERE EXISTS:

WHERE EXISTS (SELECT 1 FROM @locations WHERE ss1 = Location_tbl.Locid)

You can also say:

WHERE Location_tbl.Locid IN (SELECT ss1 FROM @locations)

Or:

INNER JOIN @locations AS x
ON x.ss1 = Location_tbl.Locid

The reason is that @locations is now, essentially, a table, not a variable or a literal value. You can't say WHERE EmployeeID IN (dbo.Employees), right? You say WHERE EmployeeID IN (SELECT EmployeeID FROM dbo.Employees).