If we develop application to select multiple data in grid and selected data to be process in stored procedure at same time, we need to pass the values of keys to stored procedure parameters. The problem is SQL Server has not array parameter. Here is some ways to resolve the problem.
- Use EXEC to execute dynamic query and passing the stored procedure parameter in WHERE clause.
- 2. Use XML as parameter and using OPENXML keyword to convert it to table.
First option is vary simple but it bad practice to use dynamic query. SQL Server can’t save statistic of dynamic query. Second option is better way but more complicated. We need simplest way to passing an array to stored procedure parameter.
Other option we can create table function to parse delimited string to table. Here is sample function to do that.
CREATE FUNCTION [dbo].[fn_ParseDelimitedStrings](@String nvarchar(MAX), @Delimiter char(1))
RETURNS @Values TABLE
(
RowId int Not Null IDENTITY(1,1) PRIMARY KEY
,Value nvarchar(255) Not Null
)
AS
BEGIN
DECLARE @startPos smallint
,@endPos smallint
IF (RIGHT(@String, 1) != @Delimiter)
SET @String = @String + @Delimiter
SET @startPos = 1
SET @endPos = CharIndex(@Delimiter, @String)
WHILE @endPos > 0
BEGIN
INSERT @Values(Value)
SELECT LTRIM(RTRIM(SUBSTRING(@String, @startPos, @endPos - @startPos)))
-- remove the delimiter just used
SET @String = STUFF(@String, @endPos, 1, '')
-- move string pointer to next delimiter
SET @startPos = @endPos
SET @endPos = CHARINDEX(@Delimiter, @String)
END
RETURN
END
This function can be used in sample stored procedure below.
CREATE PROCEDURE GetCustomers
@customerIDs AS nvarchar
AS
BEGIN
DECLARE @custIDs AS table (Value nvarchar(255) Not Null)
INSERT INTO @custIDs
SELECT Value FROM [fn_ParseDelimitedStrings](@customerIDs, ',')
SELECT * FROM Orders WHERE CustomerID IN (SELECT Value FROM @custIDs)
ENDIt very simple query. Just declare memory table and parse the delimited string into table to be used in select criteria.
I hope this tips is helpful for you.