1

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 ?

  • Which platform are you using? – Erik Darling Dec 12 '17 at 18:53
  • SQL Server 2012 – Jason Dec 12 '17 at 18:54
  • the parameters will be passed into the Stored Procedure thru an API written in C#. – Jason Dec 12 '17 at 19:07
  • 3
    Yeah, you can't really do that. You'd wanna use table valued parameters instead. – Erik Darling Dec 12 '17 at 19:07
  • 1
    You technically could if you append the string to a query string and then exec the whole thing. It's messy and doesn't get the benefits of precompiling though. – indiri Dec 12 '17 at 20:46
1

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
;
  • i'll give this a shot. thanks so much for the detailed info. – Jason Dec 14 '17 at 13:43
  • @Jason - Thank sp_BlitzErik, indiri and Dan Guzman - all I really did was roll their comments into an answer, and add a little sample code. – RDFozz Dec 14 '17 at 15:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.