February 18, 2002 at 12:36 am
I have the following table:
CREATE TABLE ITEMS ([ITEMID] int, [itRULE] varchar(1))
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (11, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (12, 3)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (21, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (22, 2)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (31, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (32, 1)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (41, 0)
INSERT INTO ITEMS (ITEMID, itRULE) VALUES (42, 0)
-- This works and gives me 11,12,21,22
SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3)
-- This doesn't works
declare @Rule varchar(10)
set @Rule='2,3'
SELECT ITEMID FROM ITEMS WHERE itRULE IN (@Rule)
Any idea?
I don't mind to change the data type if it works.
February 18, 2002 at 12:50 am
The problem is SQL Server is doing the IN comparison against the string @Rule. It's not taking it to mean:
SELECT ITEMID FROM ITEMS WHERE itRULE IN (2,3)
But rather:
SELECT ITEMID FROM ITEMS WHERE itRULE IN ('2,3')
About the only way to get around this problem that I'm aware of is with the use of dynamic SQL:
DECLARE @Rule varchar(10)
set @Rule='2,3'
DECLARE @SQL nvarchar(1000)
SET @SQL = 'SELECT ITEMID FROM ITEMS WHERE itRULE IN (' + @Rule + ')'
EXEC(@SQL)
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 18, 2002 at 4:04 am
Thanks for the solution but the real select is in a UDFunction with many lines and doesn't work properly.
February 18, 2002 at 4:48 am
Dynamic SQL won't work in a UDF. It's against the current set of rules. You always have the option of parsing the string and inserting into a table variable, then doing a join against that table variable. For instance:
CREATE FUNCTION fn_ReturnItems (@List varchar(100))
RETURNS @Items TABLE (ItemID int)
AS
BEGIN
DECLARE @match TABLE (itRule char(1))
SET @List = LTRIM(@List)
WHILE LEN(@List) > 0
BEGIN
IF (CHARINDEX(',', @List) > 0)
BEGIN
INSERT @match VALUES (LEFT(@List, CHARINDEX(',', @List) - 1))
SET @List = LTRIM(RIGHT(@List, LEN(@List) - CHARINDEX(',', @List)))
END
ELSE
BEGIN
INSERT @match VALUES (@List)
SET @List = ''
END
END
INSERT @Items
SELECT ItemID
FROM Items I JOIN @match M ON I.itRule = M.itRule
RETURN
END
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 18, 2002 at 9:47 am
SELECT ITEMID FROM ITEMS
WHERE ',' + @Rule + ',' like '%,' + convert(varchar(10),itRULE) + ',%'
This will only scan an index though.
Cursors never.
DTS - only when needed and never to control.
February 18, 2002 at 10:40 am
A much simpler solution! Great!
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply