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'
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments