How to pass string array in 'In' clause in sql stored procedure

Question

I have user table in database storing user information. I want to create stored procedure in which I will pass UserIDs as a list. I want to use this list to fetch data from user table.

I am creating type as a table using following query:

CREATE TYPE dbo.MyUserIDs AS TABLE (UserID int)

Now I am using this type in stored procedure:

ALTER PROCEDURE [dbo].[Test_in_Query]
    @MyUserids MyUserIDs READONLY 
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * From Tbl_UserMast where UserID in (select UserID from @MyUserids)
END

When I execute this code I am getting following error:

Operand type clash: int is incompatible with MyUserIDs

I am following this link as a reference: Reference link

I am directly executing stored procedure in sql server

Good question. Can you also provide an example of how you call the procedure, so that we have a minimal reproducible example? — Heinzi
– Heinzi, Commented Feb 22, 2016 at 9:25

jpw · Accepted Answer · 2016-02-22 09:50:21Z

6

I don't think you can pass a TVP-value using the SSMS gui (or at least I'm not aware of how to do it), but you have to do it in t-sql code, like so:

-- declare a variable using the user-defined type
DECLARE @MyUsers MyUserIDs 

-- insert some data into it
INSERT INTO @MyUsers(UserID) VALUES (1),(2),(4) 

-- and pass it into the proc
EXEC Test_in_Query @MyUserids = @MyUsers

answered Feb 22, 2016 at 9:50

jpw

45k6 gold badges71 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hardik Gondalia Over a year ago

Ok. That's work. My SP was correct. Just passing the value wrongly. Thank you for your help.

Collectives™ on Stack Overflow

How to pass string array in 'In' clause in sql stored procedure

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related