3

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
enter image description here

Community
  • 1
  • 1
Ubiquitous Developers
  • 3,637
  • 6
  • 33
  • 78

1 Answers1

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
jpw
  • 44,361
  • 6
  • 66
  • 86