June 8, 2005 at 1:56 pm
I need to pass a comma delimited list of N single characters to a stored procedure and then retireve records that have a field equal to one of those values.
Sample input @MemberStatus = 'M,N,F'
Table data MemberStatus M
I tried Select Field1 from Table1 where MemberStatus IN (@MemberStatus) but it didn't work.
Any ideas?
June 8, 2005 at 2:06 pm
This will help you :
June 8, 2005 at 2:09 pm
Remi I don't belive you are suggesting Dynamic SQL
How about this
CREATE PROC MyProc
(
@pParam VARCHAR(2000)
)
/* Parse the Params */
DECLARE @TblParam TABLE
(
Param VARCHAR(25)
)
WHILE CHARINDEX(',', @pParam) > 0
BEGIN
INSERT @TblParam VALUES(SUBSTRING(@pParam,1,CHARINDEX(',', @pParam) -1))
SELECT @pParam = SUBSTRING(@pParam, CHARINDEX(',', @pParam) + 1, LEN(@pParam) - CHARINDEX(',', @pParam))
END
SELECT *
FROM Table1
JOIN
@TblParam A
ON Table1.MemberStatus = A.Param
Regards,
gova
June 8, 2005 at 2:23 pm
I'm not... I thaught that there was a refference to the split function.. There is but it's not so easy to find out.
BTW the charindex is about the slowest method to do this...
Declare @ids as varchar(10)
set @Ids = '1,2'
Select * from dbo.SysObjects where id in (Select Element from dbo.Split(@Ids, ','))
CREATE FUNCTION [dbo].[Split] (@vcDelimitedString nVarChar(4000),
@vcDelimiternVarChar(100) )
/**************************************************************************
DESCRIPTION: Accepts a delimited string and splits it at the specified
delimiter points. Returns the individual items as a table data
type with the ElementID field as the array index and the Element
field as the data
PARAMETERS:
@vcDelimitedString- The string to be split
@vcDelimiter- String containing the delimiter where
delimited string should be split
RETURNS:
Table data type containing array of strings that were split with
the delimiters removed from the source string
USAGE:
SELECT ElementID, Element FROM Split('11111,22222,3333', ',') ORDER BY ElementID
AUTHOR:Karen Gayda
DATE: 05/31/2001
MODIFICATION HISTORY:
WHODATEDESCRIPTION
----------------------------------------------------------------
***************************************************************************/
RETURNS @tblArray TABLE
(
ElementIDsmallintIDENTITY(1,1), --Array index
ElementnVarChar(1200)--Array element contents
)
AS
BEGIN
DECLARE
@siIndexsmallint,
@siStartsmallint,
@siDelSizesmallint
SET @siDelSize= LEN(@vcDelimiter)
--loop through source string and add elements to destination table array
WHILE LEN(@vcDelimitedString) > 0
BEGIN
SET @siIndex = CHARINDEX(@vcDelimiter, @vcDelimitedString)
IF @siIndex = 0
BEGIN
INSERT INTO @tblArray (Element) VALUES(@vcDelimitedString)
BREAK
END
ELSE
BEGIN
INSERT INTO @tblArray (Element) VALUES(SUBSTRING(@vcDelimitedString, 1,@siIndex - 1))
SET @siStart = @siIndex + @siDelSize
SET @vcDelimitedString = SUBSTRING(@vcDelimitedString, @siStart , LEN(@vcDelimitedString) - @siStart + 1)
END
END
RETURN
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply