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

I've never used these, but wouldn't you have to SELECT from that parameter since the type is a TABLE result? — Mike Perrenoud, Oct 21 '13 at 17:52
try: `AND (Location_tbl.Locid IN (select ss1 from @locations))` — KM., Oct 21 '13 at 17:53
Like as mentioned above. You can achieve the same approach by JOIN-ing with. — Hamlet Hakobyan, Oct 21 '13 at 17:56
yes i miss the very first line in OP. BTW i delete comment as you already got the answer. — Anup Shah, Oct 21 '13 at 18:10
Why `dbo.ss(ss1)`? Those aren't very useful or meaningful names at all. And why `NVARCHAR(5)` instead of an `INT`? What if the value is greater than 5 characters? Why does it need to be Unicode? In your other question, you said these were integers, right? — Aaron Bertrand, Oct 21 '13 at 18:12

score 5 · Accepted Answer · edited May 23 '17 at 12:10

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).

while using table valued parameter getting scalar variable error in sp

1 Answers1