I have a user defined function that we use for handling comma delimitted strings of values:
CREATE FUNCTION [DBO].[UDFSplit] (@text VARCHAR(8000), @delimiter VARCHAR(20) = ' ')
RETURNS @Strings TABLE
(
position INT IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index INT
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter, @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
BEGIN
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
END
RETURN
END
Once that is created on the server, you can reference it like a table, which makes it really easy to use:
declare @ID varchar(50)
set @ID='1,2'
select E.Emp_id, E.Emp_name, L.Data from Employee E inner join code_library L
INNER JOIN UDFSPLIT(@ID, ',') ON L.code = Value WHERE L.Keys = 'Department'
ujjwaladatta
Member
229 Points
59 Posts
what is the Best way to use listbox selection in WHERE ... IN (...) clause?
Jul 25, 2012 11:16 AM|LINK
i am sending multiple selected values to a string and used in query having joins as parameter, but it is trowing an error.
in the below example i wanted to retrive employee details of 1 and 2 departments
drop table #Temp
CREATE TABLE #Temp (Dept varchar(50) NOT NULL)
declare @ID varchar(50)
set @ID='1,2'
INSERT #Temp (Dept) VALUES (@ID)
SELECT Dept FROM #Temp WHERE Dept IN (@ID) -- THIS IS WORKING
--case 1: -- THIS IS NOT WORKING
select E.Emp_id, E.Emp_name, L.Data from Employee E
inner join code_library L
on L.code in (@ID) and L.keys='Department'
-- case 2: -- THIS IS NOT WORKING
select E.Emp_id, E.Emp_name, L.Data from Employee E
inner join code_library L
on L.code in (SELECT Dept FROM #Temp) and L.keys='Department'
http://www.ujjwaladatta.wordpress.com
Mudasir.Khan
All-Star
15346 Points
3142 Posts
Re: what is the Best way to use listbox selection in WHERE ... IN (...) clause?
Jul 25, 2012 12:21 PM|LINK
try
if the Code column's data type is varchar
--case 1: -- THIS IS NOT WORKING
select E.Emp_id, E.Emp_name, L.Data from Employee E
inner join code_library L
on L.keys='Department' and L.code in (Select fn_Splict(@ID,','))
-- case 2: -- THIS IS NOT WORKING
select E.Emp_id, E.Emp_name, L.Data from Employee E
inner join code_library L
on L.code in (SELECT Dept FROM #Temp) and L.keys='Department'
TabAlleman
All-Star
15557 Points
2698 Posts
Re: what is the Best way to use listbox selection in WHERE ... IN (...) clause?
Jul 25, 2012 01:40 PM|LINK
This is a great article that shows several ways to fix your problem.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
Pyre
Member
228 Points
49 Posts
Re: what is the Best way to use listbox selection in WHERE ... IN (...) clause?
Jul 25, 2012 05:59 PM|LINK
I have a user defined function that we use for handling comma delimitted strings of values:
CREATE FUNCTION [DBO].[UDFSplit] (@text VARCHAR(8000), @delimiter VARCHAR(20) = ' ')
RETURNS @Strings TABLE
(
position INT IDENTITY PRIMARY KEY,
value varchar(8000)
)
AS
BEGIN
DECLARE @index INT
SET @index = -1
WHILE (LEN(@text) > 0)
BEGIN
SET @index = CHARINDEX(@delimiter, @text)
IF (@index = 0) AND (LEN(@text) > 0)
BEGIN
INSERT INTO @strings VALUES (@text)
BREAK
END
IF (@index > 1)
BEGIN
INSERT INTO @Strings VALUES (LEFT(@text, @index - 1))
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
ELSE
BEGIN
SET @text = RIGHT(@text, (LEN(@text) - @index))
END
END
RETURN
END
Once that is created on the server, you can reference it like a table, which makes it really easy to use:
declare @ID varchar(50)
set @ID='1,2'
select E.Emp_id, E.Emp_name, L.Data from Employee E
inner join code_library L
INNER JOIN UDFSPLIT(@ID, ',') ON L.code = Value
WHERE L.Keys = 'Department'
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: what is the Best way to use listbox selection in WHERE ... IN (...) clause?
Jul 26, 2012 05:05 AM|LINK
I think all different method and suggesation has already been posted here.
did you try Pyre method ?
Kumar Harsh
Ajay2707
Contributor
4421 Points
856 Posts
Re: what is the Best way to use listbox selection in WHERE ... IN (...) clause?
Jul 26, 2012 06:38 AM|LINK
Hi Ujjwaladatta,
To execute this type of query, you need to make string and then execute as below:
declare @string varchar(max)
set @string = 'select E.Emp_id, E.Emp_name, L.Data from Employee E
inner join code_library L
on L.code in (' + @ID + ') and L.keys='''Department'
exec @string
you can change your end.
Actually in manipulation command like inser, update this is work as a argument a subquery (select query with @string variable).
while in select ,@string is parameter and don't work directly. for this you done as above