SQL Server – How to Create SQL Stored Procedure with List Parameters

parametersql serverstored-procedures

I need to create a Stored Procedure that will take parameters of type List. For example :

Select *
 From Table
Where State   in @StateList
   or Lob     in @LobList
   or Company in @CompanyList

The parameters @StateList, @LobList and @CompanyList will contain string data like : 

StateList:[ "GA", "AL", "TN" ]
LobList:[ "D20", "PXL", "APM" ]
CompanyList:[ "QQ", "WW", "RE" ]

Does SQL allow this ?

Best Answer

There is no type you can use with SQL Server that would let you pass in and use a list as you've suggested.

You have two options:

Dynamic SQL

Pass the list in as a string value ('(12, 34, 13, 29)'). Build a nvarchar string with your SQL statement, with that value concatenated in:

SET @stmt = N'Select *
  From Table
 Where State   in ' + @StateList + N'
    or Lob     in ' + @LobList + N'
    or Company in ' + @CompanyList

Then, EXECUTE sp_executesql @stmt to run the dynamic SQL.

Use table-valued parameters

SQL Server does allow table variables to be passed, as table-valued parameters. You can pass an 'IEnumerable' as the value in your C# code (see the docs at the link for more data). Obviously, you'd need to re-write your query:

SELECT *
  FROM Table tbl
 WHERE EXISTS (SELECT 1 FROM @StateTbl where State = tbl.State)
    OR EXISTS (SELECT 1 FROM @LobTbl where Lob = tbl.Lob)
    OR EXISTS (SELECT 1 FROM @CompanyTbl where Company = tbl.Company)
;

or

SELECT tbl.*
  FROM Table tbl
         LEFT JOIN @StateTbl st ON (tbl.State = st.State)
         LEFT JOIN @LobTbl lob ON (tbl.Lob = lob.Lob)
         LEFT JOIN @CompanyTbl co ON (tbl.Company = co.Company)
 WHERE COALESCE(st.State, lob.Lob, co.Company) IS NOT NULL
;
Related Question