passing lists to stored procedures to use within IN operators. If you
pass a string such as "mike,tony,bob,dave,tim,tom" into the function
below, it will return 'mike','tony','bob','dave','tim','tom'. However,
trying to use it in conjunction with the IN operator does return any
reults. Does anyone see anything wrong with the function or perhaps
explain why it won't produce the intended results?
Thanks.
Mike
--Mike Tangorre
--August 29, 2004
CREATE FUNCTION STRING_TO_LIST
(
@LIST varchar(5000)
)
RETURNS varchar(5000)
AS
BEGIN
DECLARE @VALUE_FROM_LIST varchar(100)
DECLARE @COMMA_POSITION int
DECLARE @NEW_LIST varchar(5000)
DECLARE @NEW_VALUE varchar(100)
DECLARE @NEW_LIST_LENGTH int
DECLARE @RETURN_LIST varchar(5000)
SET @LIST = @LIST + ','
SET @NEW_LIST = ' '
-- LOOP OVER THE LIST WHILE A COMMA EXISTS
WHILE PATINDEX('%,%',@LIST) <> 0
BEGIN
-- GET THE POSITION OF THE FIRST COMMA IN THE LIST
SELECT @COMMA_POSITION = PATINDEX('%,%',@LIST)
-- GET THE VALUE TO THE LEFT OF THE COMMA
SELECT @VALUE_FROM_LIST = LEFT(@LIST,@COMMA_POSITION - 1)
-- ADD QUOTES AND A COMMA TO THE EXTRACTED VALUE
SET @NEW_VALUE = '''' + @VALUE_FROM_LIST + '''' + ','
-- PUT THE VALUE INTO A NEW LIST
SELECT @NEW_LIST = STUFF(@NEW_LIST,1,0,@NEW_VALUE)
-- CLEAR OUT THE VALUE AND THE COMMA
SELECT @LIST = STUFF(@LIST,1,@COMMA_POSITION,'')
-- END LOOP
END
-- GET THE LENGTH OF THE NEW STRING
SET @NEW_LIST_LENGTH = LEN(@NEW_LIST)
-- SELECT THE NEW STRING MINUS THE TRAILING COMMA
SET @RETURN_LIST = LEFT(@NEW_LIST,@NEW_LIST_LENGTH - 1)
-- RETURN THE LIST
RETURN @RETURN_LIST
END
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
