Possible Duplicate:
Pass a list-structure as an argument to a stored procedure
I am having a session variable that contains multiple comma separated values.I stored these values in an array using Split function.Now I need to pass this array to the stored procedure.Is this possible and if yes how can I use this array in stored procedure?
If you are using Sql Server 2008, try passing it as a table value parameter:
I've ran into this exact same scenario before and here is what I did:
Instead of splitting the variable into an array in code I simply passed the entire string of comma delimited text to a stored procedure as an nvarchar. Then I created a database function that takes the value passed in as well as a delimiter and returns a table of the splitted values. See below...
CREATE FUNCTION [dbo].[split]
(
@DataToSplit NVARCHAR(MAX),
@Delimiter NVARCHAR(5)
)
RETURNS @ReturnVal AS TABLE
(
ID INT IDENTITY(1, 1),
Item NVARCHAR(200)
)
AS
BEGIN
WHILE (CHARINDEX(@DELIMITER, @DataToSplit) > 0)
BEGIN
INSERT INTO @ReturnVal
(
Item
)
VALUES
(
SUBSTRING(@DataToSplit, 1, CHARINDEX(@Delimiter, @DataToSplit) - 1)
)
SET @DataToSplit = SUBSTRING(@DataToSplit, CHARINDEX(@Delimiter, @DataToSplit) + 1, LEN(@DataToSplit))
END
-- Grab the last item of the separated list of items
INSERT INTO @ReturnVal
(
Item
)
VALUES
(
@DataToSplit
)
RETURN
END
Once you have your split function created you can use it in the stored proc that you are passing the delimited text into and use it in various ways such as...
CREATE PROCEDURE [dbo].[ExampleProc]
@Values NVARCHAR(MAX)
AS
SET NOCOUNT ON;
SELECT Item
FROM [dbo].[split](@Values, ',')
-- OR
SELECT Item
FROM [dbo].[SomeTable]
WHERE Item IN
(
SELECT Item FROM [dbo].[split](@Values, ',')
)
-- OR
SELECT a.Item
FROM [dbo].[SomeTable] AS a
INNER JOIN [dbo].[split](@Values, ',') AS b
ON a.ID = b.ID
GO
