CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduresssrs-2008
Simsons
Simsons

Reputation: 12735

Invalid column name for table type parameter

I have a table type which is used in a stored procedure to filter out values.

CREATE TYPE PackageIdType AS TABLE   
( 
     PackageId VARCHAR(150)  
);

My stored procedure is this:

CREATE PROCEDURE [dbo].[spLocalGetValuesFromTable]    
     @RundateStart datetime,
     @RundateEnd datetime,
     @CreationIds PackageIdType READONLY  
AS
     SELECT * 
     FROM MYTABLE 
     WHERE date BETWEEN @RundateStart AND @RundateEnd 
       AND Ids IN (@CreationIds)

But when I run this getting error:

Msg 207, Level 16, State 1, Procedure spLocalGetValuesFromTable, Line --[Batch Start Line 0]
Invalid column name '@CreationIds'

Upvotes: 1

Views: 948

Answers (1)

cf_en
cf_en

Reputation: 1661

It's a table, so the correct syntax would be

SELECT *
FROM  MYTABLE
WHERE date Between @RundateStart And @RundateEnd 
  AND Ids in (SELECT PackageId FROM @CreationIds)

This assumes that each row in the table @CreationIds is an Id that can map to the same type as Ids in MYTABLE.

Upvotes: 3

Related Questions