What is the best way to select any number of filters for a specific Id on a query in sql server environment.
21 April 2011 1:14
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.
select * from tblOrders where OrderId = 1 or OrderId = 2 or OrderId = 1000 etc...
21 April 2011 7:18
You can replace this "OR" condition with "IN" statement. But you need to use one additional function to get the values.
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]
RETURNS @ArrayTable Table
--Declare Function variable
DECLARE @Delimiter VARCHAR(10)
SET @Delimiter = ','
--Get the string
DECLARE @data NVARCHAR(MAX)
SET @data = @ArrayString
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)
/*------------- UNIT TEST CASE -------------------------
CREATE TABLE #ABC
INSERT INTO #ABC
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
- Mishra, Sandeep(Success is a journey, not a destination.:)
- Ditandai sebagai Jawaban oleh mnoon 13 Oktober 2011 0:07
29 April 2011 6:41
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.
29 April 2011 9:20
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.
- Mishra, Sandeep. (Success is a journey, not a destination.:)