Passing a comma delimited string to a function for a IN() clause
-
I hav this function:
ALTER FUNCTION fnEmployeesByGroupCode (@GroupCode as varchar(50))
RETURNS TABLE AS
RETURN (
SELECT TOP 100 PERCENT dbo.CRM_Groups.Group_Code,
dbo.Employee.Employee_Id, dbo.Employee.Job_Title,
dbo.Employee.Team_Number,
dbo.Employee.Full_Name
FROM dbo.Employee INNER JOIN dbo.CRM_Group_Assignments
ON dbo.Employee.Employee_Id = dbo.CRM_Group_Assignments.Employee_Id
INNER JOIN dbo.CRM_Groups
ON dbo.CRM_Group_Assignments.CRM_Group_Id = dbo.CRM_Groups.CRM_Groups_Id
WHERE (dbo.CRM_Groups.Group_Code in(@GroupCode))
ORDER BY dbo.Employee.Team_Number, dbo.Employee.Full_Name
 
I am having a problem with passing a comma delimited list to the function to be used in the IN() clause for the record selection. I have tried every combination of quotes I can thin of when calling the function. Does anyone have an answer?
Thanks
-
I am not fun of what you are doing here but this is probably what you are looking for:
SELECT TOP 100 PERCENT dbo.CRM_Groups.Group_Code,
dbo.Employee.Employee_Id, dbo.Employee.Job_Title,
dbo.Employee.Team_Number,
dbo.Employee.Full_Name
FROM dbo.Employee INNER JOIN dbo.CRM_Group_Assignments
ON dbo.Employee.Employee_Id = dbo.CRM_Group_Assignments.Employee_Id
INNER JOIN dbo.CRM_Groups
ON dbo.CRM_Group_Assignments.CRM_Group_Id = dbo.CRM_Groups.CRM_Groups_Id
WHERE CHARINDEX(',' + Convert(varchar, dbo.CRM_Groups.Group_Code) + ',', @GroupCode) > 0
ORDER BY dbo.Employee.Team_Number, dbo.Employee.Full_Name
I tossed the convert in just in case groupcode was not type Char
* Noel -
When I call this function to return a recordset I am using:
SELECT * from fnEmployeesByGroupCode ('CMS,CM')
I keep getting an empty recordset even though I know there are records with either a group code of CMS or CM. What am I doing wrong in passing the parameter?
-
A *virtual friend* of mine has written a nice article on arrays and lists in SQL Server. Might it is of some help for you
http://www.sommarskog.se/arrays-in-sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
David Hardin wrote a great script to convert an array into a table which can then be joined to your other tables. I use the script often. see
http://www.sqlservercentral.com/scripts/contributions/100.asp
Francis
-
From what I wrote I forgot to mention you have to do either:
SELECT * from fnEmployeesByGroupCode (',CMS,CM,')
OR Better yet:
SELECT TOP 100 PERCENT dbo.CRM_Groups.Group_Code,
dbo.Employee.Employee_Id, dbo.Employee.Job_Title,
dbo.Employee.Team_Number,
dbo.Employee.Full_Name
FROM dbo.Employee INNER JOIN dbo.CRM_Group_Assignments
ON dbo.Employee.Employee_Id = dbo.CRM_Group_Assignments.Employee_Id
INNER JOIN dbo.CRM_Groups
ON dbo.CRM_Group_Assignments.CRM_Group_Id = dbo.CRM_Groups.CRM_Groups_Id
WHERE CHARINDEX(',' + Convert(varchar, dbo.CRM_Groups.Group_Code) + ',', ','+@GroupCode+',') > 0
ORDER BY dbo.Employee.Team_Number, dbo.Employee.Full_Name
and your select statement will be:
SELECT * from fnEmployeesByGroupCode ('CMS,CM')
* Noel -
I have two helper functions I use.
Then you can do a
SELECT ITEM
FROM fnc_10_comma_delimited_int('1,3,5,6' , ',' )
(or use the string version for strings)
You get the point I think. below are the 2 scripts.
if exists (select * from sysobjects where id = object_id('dbo.fnc_10_comma_delimited_char') and xtype = 'TF')
drop function dbo.fnc_10_comma_delimited_char
GO
CREATE FUNCTION fnc_10_comma_delimited_char(@list varchar(8000), @Delimiter VARCHAR(10) = ',')
RETURNS @tablevalues TABLE
(item varchar(8000))
AS
BEGIN
DECLARE @P_item varchar(255)
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues
SELECT Item = @p_Item
END
RETURN
END
GO
--GRANT EXECUTE ON fnc_10_comma_delimited_char TO someUser
if exists (select * from sysobjects where id = object_id('dbo.fnc_10_comma_delimited_int') and xtype = 'TF')
drop function dbo.fnc_10_comma_delimited_int
GO
CREATE FUNCTION fnc_10_comma_delimited_int(@list varchar(8000), @Delimiter VARCHAR(10) = ',')
RETURNS @tablevalues TABLE
(item int)
AS
BEGIN
DECLARE @P_item varchar(255)
WHILE (DATALENGTH(@list) > 0)
BEGIN
IF CHARINDEX(@Delimiter,@List) > 0
BEGIN
SELECT @p_Item = SUBSTRING(@List,1,(CHARINDEX(@Delimiter,@List)-1))
SELECT @List = SUBSTRING(@List,(CHARINDEX(@Delimiter,@List) + DATALENGTH(@Delimiter)),DATALENGTH(@List))
END
ELSE
BEGIN
SELECT @p_Item = @List
SELECT @List = NULL
END
INSERT INTO @tablevalues
SELECT Item = convert(int,@p_Item)
END
RETURN
END
GO
--GRANT EXECUTE ON fnc_10_comma_delimited_int TO someUser
-
Thanks for all the help. Noeld's answer worked great. I am passing only a couple of codes in the parameter string so this was the easiest to use and I get the results need.
The other solutions will be helpful for passing a longer array to the procedure. Good to know.
-
ALTER FUNCTION dbo.fnStringToTable(@CommaDelimList varchar(5000), @Delim char(1))
--*********************************
--Example;
--select *
--from dbo.fnStringToTable('one, two, three, four', ',')
--
--Author: Ed Hellyer
--Date: Tuesday July 26th 2006
--
--*********************************
RETURNS @List TABLE (Value varchar(100))
AS
BEGIN
declare @Start int
set @Start = 1
while @Start is not null
begin
insert into @List(Value) select LTrim(RTrim(SubString(@CommaDelimList, @Start, abs(@Start - IsNull(NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0), Len(@CommaDelimList) + 1)))))
set @Start = NullIf(CharIndex(@Delim, @CommaDelimList, @Start + 1), 0) + 1
end
return
END
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply