Query Problem in sqlserver 2000
-
Hello everyone, i m using SQL Server 2000, in this i have a table with following fields and data
Table1:
BuyerID 1
ContactName JHall
PhoneNo 1234567
Email abc@hotmail.com
IsCLIEnabled 1
Clis 1234567,7896541,9632581,5478126
now i write the follwing query:
declare @Clis varchar(1000)
select @Clis=coalesce(@Clis+',','')+CLIs from Table1
where BuyerID=1
now this query returns more than ome CS values in 1234567,7896541,9632581,5478126 this form. (i mean i getting these values: 1234567,7896541,9632581,5478126 in this @Clis variable )
after this i write::
if charindex(@Cli,@Clis)>0
print 'Cli Found in List'
else
raiserror('Cli Not Found',16,1)
but this check not satified my requirments as whenever i give
if charindex(12345,'123456,23478,145678')>0
it always return one it match some character , i want to match exact value as if 12345 is present in the list it returns 1
otherwise display error.plz tell me how i get Comma separated values stored in an array and check user entered cli within the list one by one through loop or stored this CSV list in a seperate table and check them
plz give me best solution to solve this problem
its very urgent
plz reply me asap
-
Isa,
The best solution for this, is to approach for a set based solution rather than using performance degrading cursors....
Here i've got one function which does the trick for me....
CREATE FUNCTION udf_SplitItems
(
@strInputValues VARCHAR(8000) , --list of delimited items
@strDelimiter CHAR(1) = ',' --delimiter that separates items
)
RETURNS
@tblList TABLE ( ItemNo SMALLINT IDENTITY( 1 , 1 ) NOT NULL PRIMARY KEY CLUSTERED , Item VARCHAR(8000)) WITH SCHEMABINDING
AS
BEGIN
DECLARE @strItem VARCHAR(8000)
DECLARE @iPos SMALLINT -- Current Starting Position
DECLARE @iNextPos SMALLINT -- Position of Next Delimiter
DECLARE @iLenInput SMALLINT -- Length of Input Items
DECLARE @iLenNext SMALLINT -- Length of Next Item
DECLARE @iDelimLen TINYINT -- Length of The Delimiter
SELECT
@iPos = 1 ,
@iDelimLen = LEN( @strDelimiter ) , -- Usually 1
@iLenInput = LEN( @strInputValues ) ,
@iNextPos = CHARINDEX( @strDelimiter , @strInputValues , 1 )
-- Doesn't Work for Space as a Delimiter
IF ( @strDelimiter = ' ' )
BEGIN
INSERT INTO @tblList( Item )
SELECT 'ERROR: Blank is not a valid delimiter'
RETURN
END
-- Loop Over the Input , Until the Last Delimiter.
WHILE @iPos <= @iLenInput AND @iNextPos > 0
BEGIN
IF @iNextPos > @iPos
BEGIN
SET @iLenNext = @iNextPos - @iPos
SET @strItem = LTRIM( RTRIM( SUBSTRING( @strInputValues , @iPos , @iLenNext ) ) )
IF ( LEN( @strItem ) > 0 )
BEGIN
INSERT INTO @tblList ( Item )
SELECT @strItem
END
END
-- Position Over the Next Item
SELECT
@iPos = @iNextPos + @iDelimLen,
@iNextPos = CHARINDEX( @strDelimiter , @strInputValues , @iPos )
END
-- Now There Might be One More Item Left
SET @strItem = LTRIM( RTRIM( SUBSTRING( @strInputValues, @iPos, @iLenInput-@iPos + 1 ) ) )
-- Put the Last Item in, If Found
IF ( LEN( @strItem ) > 0 )
BEGIN
INSERT INTO @tblList ( Item )
SELECT @strItem
END
RETURN
END
GO
CREATE PROCEDURE usp_CheckClientForBuyer
(
@BuyerID tinyint ,
@Cli varchar(100)
)
AS
BEGIN
DECLARE @Clis VARCHAR(1000)
-- create temp table to hold values
CREATE TABLE #Table1
(
BuyerID tinyint not null primary key ,
ContactName varchar(100) not null ,
PhoneNo varchar(100) not null ,
Email varchar(100) not null ,
IsCLIEnabled bit not null ,
Clis varchar(1000) null
 
-- Populate table with some data
INSERT INTO #Table1( BuyerID , ContactName , PhoneNo , Email , IsCLIEnabled , Clis )
SELECT 1 , 'JHall' , '1234567' , 'abc@hotmail.com' , 1 , '1234567,7896541,9632581,5478126'
UNION ALL
SELECT 2 , 'XMark' , '9867967' , 'xyz@hotmail.com' , 1 , '2332581,6178126'
-- get csv values
SELECT @Clis = COALESCE( Clis , '' ) FROM #Table1 WHERE BuyerID = @BuyerID
PRINT @Clis
-- check for any matching clients this buyer
-- check here we used a table valued fn to convert comma seperated values into rows
IF EXISTS( SELECT * FROM #Table1 WHERE BuyerID = @BuyerID AND @Cli IN ( SELECT Item FROM udf_SplitItems( @Clis , ',' ) ) )
PRINT 'Cli ' + @Cli + ' Found.'
ELSE
PRINT 'Cli ' + @Cli + ' Not Found.'
-- clear the junk tables
DROP TABLE #Table1
END
GO
EXEC usp_CheckClientForBuyer 1 , '1'
GO
EXEC usp_CheckClientForBuyer 2 , '2332581'
GO
--Ramesh
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply