Binaryworld - How to pass array to stored procedure in SQL Server ... [ SQL Server -> T-SQL ]

Have you ever come across the situaction when you have to pass mutiple values as parameter to your stored procedure.

Assume the following scenario.

- Your application pass comma seperated CustomerID list to stored procedure to get Orders for related customers.

Above requirement can be done in couple of ways. In this artical we will see 3 different approaches to perform this task.

Method-1 , Method-2 use UDF (User Defined Function) to Parse Array values and returns Table. Which can be used in where clause or JOIN.

Method-3 is to build dynamic SQL

Run the following script to test three different approaches

Click here to copy the following block

Use Master
Go
--// example : select * from dbo.ParseArray('1|2|3|4','|') as tmp

CREATE FUNCTION ParseArray
(@Array varchar(8000),
@separator char)
RETURNS @ParsedArrays TABLE (row_counter int identity(1,1), array_value varchar(8000))
AS
BEGIN

-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(8000) -- this holds each array value as it is returned

--create table #ParsedArrays (array_Value varchar(8000))

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
insert @ParsedArrays(array_value) VALUES (@array_value)

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
RETURN
END
GO

--//////////////////////////////////////////////////////////////////////////////////////////
Use Northwind
Go
Create proc usp_GetOrders
@CustID_List varchar(500)=''
As
-- Method-1 : Best Approach , Execution Plan is cached after first run so any executions after that are faster
Select o.* from Orders o
Where CustomerID in(Select array_value from master.dbo.ParseArray(@CustID_List,',') as tmp)

-- Method-2 : Same as Method-1 but using JOIN instead of IN
Select o.* from Orders o JOIN master.dbo.ParseArray(@CustID_List,',') as tmp ON o.CustomerID =tmp.array_value

-- Method-3 : Dynamic SQL, Execution Plan is is not cached and re-compiled everytime. Slow approach. SQL is Not readable.
-- Note: use single quote to split string values.
Declare @sql varchar(max)
set @sql='Select o.* from Orders o Where CustomerID in(''' + replace(@CustID_List,',' , ''',''') + ''')'
execute(@sql)
go

--Test
exec usp_GetOrders 'ALFKI,VINET'