What is the best way to select any number of filters for a specific Id on a query in sql server environment.

Question

I'm trying to select any number of orders and call a stored procedure only one time.  So my question is in order to peform a select statement in  a stored procedure, is it possible to have n number of parameters, and if it is possible what is the best way to call a stored procedure with any number of orderIds?  Or is it better to send in a nvarchar 30000 with xml and parse the xml for each order id and stick it into my stored procedure that way?  I'm looking for a better way to perform this operation that is reliable.

example:

select * from tblOrders where OrderId = 1 or OrderId = 2 or OrderId = 1000 etc...

---

Marc Noon

Answer 1

Dear Marc,

You can replace this "OR" condition with "IN" statement. But you need to use one additional function to get the values.

Like:

select * from tblOrders where OrderId IN (1, 2, 1000)

To get 1, 2 and etc..you need to pass a string to a function and get the table to pass to IN statement like below

select * from tblOrders where OrderId IN (SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data)

Find Script for this:

ALTER FUNCTION [dbo].[UDF_Get_ArrayTable]
(
    @ArrayString    VARCHAR(MAX)
)

--Return Table   
RETURNS @ArrayTable Table
(
    ArrayData INT
)

AS

BEGIN

    --Declare Function variable
    DECLARE @Delimiter    VARCHAR(10)
    SET @Delimiter = ','
   
    --Get the string
    DECLARE @data NVARCHAR(MAX)
    SET @data = @ArrayString
   
    --using xml
    DECLARE @textXML XML;
    SELECT @textXML = CAST('<d>' + REPLACE(@data, @Delimiter, '</d><d>') + '</d>' AS XML);

    --Return Array Table
    INSERT INTO @ArrayTable(ArrayData)
    SELECT LTRIM(RTRIM(T.split.value('.', 'NVARCHAR(MAX)'))) AS ArrayData
    FROM @textXML.nodes('/d') T (split)
   
    --Return Function
    RETURN
END

/*------------- UNIT TEST CASE -------------------------

CREATE TABLE #ABC
(
    ID INT
)
INSERT INTO #ABC
SELECT 1
UNION
SELECT 2

DECLARE @data NVARCHAR(MAX), @delimiter NVARCHAR(5)
SELECT @data = '1,2,3,4,5'

SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data) -- return from Function.

SELECT * FROM #ABC
WHERE ID IN (SELECT ArrayData FROM dbo.UDF_Get_ArrayTable(@data)) -- Check with Data
--------------------------------------------------------*/

Please verify this and try. Hope helps you

Thank you.

---

- Mishra, Sandeep(Success is a journey, not a destination.:)

Answer 2

I basically did the same thing, just pulled everything into a single varchar(max) comma delimited, and parsed each id and placed that id into a temp  table which has only one column ie an id column sort of like what you did, but i used an inner join with my temp table. I did have to cast to int in order for the inner join to be very fast and zippy as this is geared for production.

---

Marc Noon

Answer 3

Hi Marc, Sounds good to me. Inner join is better way.

Hope your problem solved as you are following correct way. Please mark as answer if my answer help you or same as what you are following.

Thank you!

---

- Mishra, Sandeep. (Success is a journey, not a destination.:)