December 31, 2008 at 7:44 am
Dear,
Trying to find out a solution to the fact that I needed to pass to a stored procedure a series of strings to be used in a "IN Clause", browsing the Internet I found out the code of a User-defined function that splits the delimited parameter list and returned a table to be used in the SELECT statement.
However, when I try to save the stored procedure, I obtain the Error 446: Cannot resolve collation conflict for equal to operation message.
Could anybody help me?
Here are the details:
1. User-defined function:
CREATE FUNCTION dbo.fct_ListToTable (@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table(
Valuevarchar(8000) not null
)
AS
BEGIN
--Declare helper to identify the position of the delim
DECLARE @DelimPosition INT
--Prime the loop, with an initial check for the delim
SET @DelimPosition = CHARINDEX(@delim, @list)
--Loop through, until we no longer find the delimiter
WHILE @DelimPosition > 0
BEGIN
--Add the item to the table
INSERT INTO @listTable(Value)
VALUES(RTRIM(LEFT(@list, @DelimPosition - 1)))
--Remove the entry from the List
SET @list = right(@list, len(@list) - @DelimPosition)
--Perform position comparison
SET @DelimPosition = CHARINDEX(@delim, @list)
END
--If we still have an entry, add it to the list
IF len(@list) > 0
insert into @listTable(Value)
values (RTRIM(@list))
RETURN
END
2. Stored procedure:
CREATE PROCEDURE dbo.sp_test
(
@in_listnvarchar(512)= ''
)
AS
BEGIN
SELECT
*
FROM
tbl_category
WHERE
category_name in (
SELECT
Value
FROM
dbo.fct_ListToTable(@in_list, ',')
)
END
GO
3. Definition of the columns
TABLE tbl_category
....
category_name nvarchar(512) not null
In advance, many thanks for any help.
Didier
December 31, 2008 at 8:07 am
It looks like your collation settings for the table are different than that for the database, check that these are the same.
December 31, 2008 at 8:27 am
Thanks for your answer but could you be a little bit more explicit? What do I have to check?
thanks in advance,
December 31, 2008 at 8:32 am
Check the properties of the database (right click), have a look at the collation setting.
Check the properties of the table --> Extended properties, have a look at the collation setting.
Check that these are the same..
December 31, 2008 at 8:40 am
You saved my day...
Here is the modification I needed to apply to the User-Defined function:
CREATE FUNCTION dbo.fct_ListToTable (@list as varchar(8000), @delim as varchar(10))
RETURNS @listTable table (
Valuevarchar(8000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
)
AS
BEGIN
--Declare helper to identify the position of the delim
DECLARE @DelimPosition INT
--Prime the loop, with an initial check for the delim
SET @DelimPosition = CHARINDEX(@delim, @list)
--Loop through, until we no longer find the delimiter
WHILE @DelimPosition > 0
BEGIN
--Add the item to the table
INSERT INTO @listTable(Value)
VALUES(RTRIM(LEFT(@list, @DelimPosition - 1)))
--Remove the entry from the List
SET @list = right(@list, len(@list) - @DelimPosition)
--Perform position comparison
SET @DelimPosition = CHARINDEX(@delim, @list)
END
--If we still have an entry, add it to the list
IF len(@list) > 0
insert into @listTable(Value)
values (RTRIM(@list))
RETURN
END
thanks to the "COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL" statement, it now works perfectly !
Once again, thanks a lot.
Didier
December 31, 2008 at 8:45 am
No worries, glad that it worked out for you.
Collation affects the way that string values are sorted and compared and can sometimes can cause funny issues.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply