PROBLEM in Splitting Comma Seperate Values(CSV) in Sql Server 2005

Question

PROBLEM in Splitting Comma Seperate Values(CSV) in Sql Server 2005

Aswanth

Mr or Mrs. 500

Points: 548
More actions
June 21, 2008 at 12:11 am

#189471

HI..
i'm having a Following table in Sql Server-2005...
( Emp_details --->Table Name)
slNo Emp_ID Emp_Name Emp_cat
1 1 1 1,2,5,9
2 1 1 4,7,8,9,10,15
3 2 5 6,52,10,64,78
4 2 5 7,9,5,8,1
5 3 7 1,2,8,7
6 3 7 3,5,7
7 3 7 2,4,6,87,4,31,46,7
Now I want the Stored Procedure in Such a way that ,
If i select the Emp_ID, Emp_ID i want to get the Corresponding Emp_Cat values in Splitted format..
Like: I'm selecting sl.No=5 & Emp_ID=3
O/P:
Emp_Name Emp_ID Emp_Cat
7 3 1
7 3 2
7 3 8
7 3 7
------------------------------------------------------------------------------------------------
This is the Stored Procedure i'm Using..
Create PROCEDURE Get_CSV_Data
(
@Emp_Name int,
@empid int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @Item Varchar(8000)
DECLARE @sInputList varchar(8000)
DECLARE @Delimiter char(1)
SET @sInputList=
(select emp_cat from test where Emp_Name =@Emp_Name and empid=@empid)
SET @Delimiter = ','CREATE TABLE #List(Emp_Name int,empid int,Item varchar(8000)) -- Create a temporary table
WHILE CHARINDEX(@Delimiter,@sInputList,0) <> 0
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@Delimiter,@sInputList,0)-1))),
@sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@Delimiter,@sInputList,0)+1,LEN(@sInputList))))
IF LEN(@Item) > 0
INSERT INTO #List values(@Emp_Name ,@empid,@Item)
END IF LEN(@sInputList) > 0
INSERT INTO #List values(@Emp_Name ,@empid,@sInputList)
SELECT * FROM #List
DROP TABLE #List
RETURN
END
--------------------------------------------------------------------------------
ERROR:
while executing the SP i'm getting Error like:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
I may have Duplicate Records in MY Table..
So How to Change this SP...
Please Help Me in doing this..
Thank You..

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

Please read the following article on Tally tables... there's also a script that demonstrates how to split a whole table's worth of CSV's, just like you have, without even coming close to a cursor or While loop...

http://www.sqlservercentral.com/articles/TSQL/62867/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)