Hiya,
WHat about using CHARINDEX() and looping through the varchar list converting
each element of the list into an int and then using this element in your SQL
'where periodID = @i'.
Maybe not exactly what you want to do but I have used this method before and
it works.
-- Declare Local Variables
DECLARE @i AS int -- used as index in loop
, @l_@idlist AS varchar(2000) -- local var to use to build
up the list of ids
BEGIN
-- loop through list of job ids and assign each one to @i
IF LEN(@linklist) > 0
BEGIN
SELECT @l_idlist = @idlist
WHILE CHARINDEX(',', @l_idlist) > 0
BEGIN
SELECT @i = CONVERT(int, SUBSTRING(@l_idlist, 1, CHARINDEX(',',
@l_idlist)-1))
IF @i > 0
BEGIN
-- Do whatever SQL operation you need to here
SELECT/INSERT/UPDATE/DELETE
FROM tablename
WHERE periodID = @i
-- remove first element of list and then build up list again
SELECT @l_linklist = SUBSTRING(@l_idlist,CHARINDEX(',',
@l_idlist)+1, LEN(@l_idlist)-(CHARINDEX(',',@l_idlist)))
IF @@error <> 0
BEGIN
GOTO ERROR_EXIT
END
END
END
END
-- end of loop
-- deal with last code from list of ids
IF LEN(@l_idlist) > 0
BEGIN
SELECT @i = CONVERT(int, @l_idlist)
IF @i > 0
BEGIN
-- Do whatever SQL operation you need to here
SELECT/INSERT/UPDATE/DELETE
FROM tablename
WHERE periodID = @i
END
END
HTH
Stephen
-----Original Message-----
From: Joel Firestone [mailto:[EMAIL PROTECTED]]
Sent: 03 April 2002 15:51
To: SQL
Subject: Passing a list to a stored procedure
SQL-Talkers:
I have a stored procedure that I'm trying to pass a list to. I tried to set:
@idlist varchar and @idlist int
I need to use the list in an IN statement, like:
where periodID IN ( @idlist )
When I have it set to varchar, I get a conversion error. When I set it to
int,
the query runs, but never returns anything.
Any help would be appreciated. Thanks.
Joel
______________________________________________________________________
Get the mailserver that powers this list at http://www.coolfusion.com
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
