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..

• Jeff Moden

  SSC Guru

  Points: 1000539

  More actions

  June 21, 2008 at 12:25 am

  #832171

  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://qa.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)
  Intro to Tally Tables and Functions