Search in a table using two parameters where one of them is a list of integers

Question

I am trying to create a stored procedure to use in my web api using ASP.NET and C#. I have a table where there are three columns like this:

Id |TranName|ConfigName
---|--------|----------
10 |ABC     |abcd
11 |BDE     |bdef
12 |FGH     |fghi
13 |HIJ     |hijk

The problem is that I am using another stored procedure that returns a list of integers which I have to use to search the above table along with the TranName. So for example, given an integer list 9,10,11,12,13 and TranName BDE find the corresponding ConfigName (in this case I expect it to match 11 for the Id then BDE for the TranName and return bdef as the ConfigName.

The code I have for the stored procedure is as shown below. I am creating a Table to be used as a parameter to search for in the TransTable.

CREATE TYPE dbo.ScrapeTranList
AS TABLE

(
  scrapeTranId int
);
GO
CREATE PROCEDURE [dbo].[GetConfigNameById]
    -- Add the parameters for the stored procedure here
    @list AS dbo.ScrapeTranList READONLY,
    @TransName varchar = null
AS
BEGIN
    SET NOCOUNT ON;
select ConfigName 
from TransTable as T 
where T.TranName = @TransName AND T.Id IN @list
END

Can someone help me fix the Stored Procedure code?

Answer 1

You should JOIN your table to table-valued parameter like this:

select ConfigName 
from TransTable as T join @list L
        on T.Id = L.scrapeTranId
where T.TranName = @TransName