Hi Folks,
Today i was trying to pass multi-value parameter into my select query in SQL Server. To accomplish this i stored the multi-value parameter in a comma delimited string. Then, i want to pass it in my IN clause of the select statement. here is an example to what i want to accomplish:
Delcare @myVar varchar(500)
Set @myVar='1,2,3,4,5,7'
Select * from Employee
where EmployeeId IN (@myVar)
You will get this error:
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value '1,2,3,4,5,7' to data type int.
It makes sense, because i have a varchar that holds all my values and i want to pass those in the IN clause that only accepts integer values!.
The solution for this problem is to parse this string into set of integers and pass it back to your query in your IN clause. For this reason, i have created a function that return a table of 1 column of type int. This will be passed back to the select statement i have mentioned above.
The function code to parse comma delimited string into set of integers:
-- =============================================
-- Author: Mostafa Elzoghbi
-- Create date: 12/10/2012
-- Description: Parse a string into set of numbers
-- =============================================
CREATE FUNCTION [dbo].[ParseCommaDelimitedString]
( @CommaSeparatedStr Today i was trying to pass multi-value parameter into my select query in SQL Server. To accomplish this i stored the multi-value parameter in a comma delimited string. Then, i want to pass it in my IN clause of the select statement. here is an example to what i want to accomplish:
Delcare @myVar varchar(500)
Set @myVar='1,2,3,4,5,7'
Select * from Employee
where EmployeeId IN (@myVar)
You will get this error:
Msg 245, Level 16, State 1, Line 7
Conversion failed when converting the varchar value '1,2,3,4,5,7' to data type int.
It makes sense, because i have a varchar that holds all my values and i want to pass those in the IN clause that only accepts integer values!.
The solution for this problem is to parse this string into set of integers and pass it back to your query in your IN clause. For this reason, i have created a function that return a table of 1 column of type int. This will be passed back to the select statement i have mentioned above.
The function code to parse comma delimited string into set of integers:
-- =============================================
-- Author: Mostafa Elzoghbi
-- Create date: 12/10/2012
-- Description: Parse a string into set of numbers
-- =============================================
CREATE FUNCTION [dbo].[ParseCommaDelimitedString]
nvarchar(1000) =NULL)
RETURNS @myTable TABLE ([Id] [int] NOT NULL)
AS
BEGIN
declare @pos int
declare @piece varchar(500)
-- Need to tack a delimiter onto the end of the input string if one doesn't exist
if right(rtrim(@CommaSeparatedStr ),1) <> ','
set @CommaSeparatedStr = @CommaSeparatedStr + ','
set @pos = patindex('%,%' , @CommaSeparatedStr )
while @pos <> 0
begin
set @piece = left(@CommaSeparatedStr , @pos - 1)
-- You have a piece of data, so insert it, print it, do whatever you want to with it.
insert @myTable
select @piece
set @CommaSeparatedStr = stuff(@CommaSeparatedStr , 1, @pos, '')
set @pos = patindex('%,%' , @CommaSeparatedStr )
end
RETURN
END
-- =============================================
What you need after that to re-write your query as follows:
Select *
from Employee
where EmployeeId IN
( select * from ParseCommaDelimitedString(@myVar))
Now, you will be able to pass any comma delimited string, varchar,nvarchar to this function and it returns table of integer that you can set in the IN clause of you select statement or any other T-SQL statements you works with your logic.
Hope this helps.
--ME
