逗号分隔符在程序中 [英] Comma Seperator in procedure

问题描述

我有一个用逗号（，）分割数据的程序..



存储过程

I have a procedure to split the data with comma(,)..

Stored Procedure

Create Procedure SM_SP_NewServerInsertion --'test','kdinesh','2014-01-01 00:00:00.000','test100,test200','test400,test500'  
(  
 @strServerName VARCHAR(100)  
,@strCreatedby VARCHAR(100)  
,@strCreatedDate datetime  
,@strServiceName VARCHAR(200)  
,@strDescription VARCHAR(500)  
)  
AS  
BEGIN  
Declare @INDEX1 int
Declare @INDEX2 int
Declare @Delimiter char=','
Declare @SLICE1 varchar(100)
Declare @SLICE2 varchar(100)

 INSERT INTO tbl_servername(servername,createdby,createddate)   
 VALUES  
 (  
  @strServerName  
 ,@strCreatedby  
 ,@strCreatedDate  
 )  
  
SELECT @INDEX1 = 1
SELECT @INDEX2 = 1

WHILE @INDEX1 !=0 and @INDEX2 !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX1 = CHARINDEX(@Delimiter,@strServiceName)
SELECT @INDEX2 = CHARINDEX(@Delimiter,@strDescription)

-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX1 !=0 and @INDEX2 !=0
begin
SELECT @SLICE1 = LEFT(@strServiceName,@INDEX1 - 1)
SELECT @SLICE2 = LEFT(@strDescription,@INDEX2 - 1)
End

ELSE
begin
SELECT @SLICE1 = @strServiceName
SELECT @SLICE2= @strDescription
End

-- PUT THE ITEM INTO THE RESULTS SET
--INSERT INTO @Results(Items) VALUES(@SLICE1)
--INSERT INTO @Results(Items) VALUES(@SLICE2)


 INSERT INTO tbl_servicename(serverid,servicename,description,createdby,createddate)   
 VALUES  
 (  
  scope_identity()  
 ,@SLICE1  
 ,@SLICE2  
 ,@strCreatedby  
 ,@strCreatedDate  
 )  

-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @strServiceName = RIGHT(@strServiceName,LEN(@strServiceName) - @INDEX1)
SELECT @strDescription = RIGHT(@strDescription,LEN(@strDescription) - @INDEX2)

-- BREAK OUT IF WE ARE DONE
IF LEN(@strServiceName) = 0 and LEN(@strDescription) = 0  BREAK
END

  
  
END  

我的第一个参数工作正常（@ SLICE1），但第二个参数是不工作而不是它将使用逗号分隔符返回所有值。

My first parameter working fine(@SLICE1) but second parameter is not working. instead of that it will return the all values with comma separator.

推荐答案

通过说第二个参数不工作不确定你的意思...但是，它不是问题。



问题出在其他地方。使用WHILE循环已过时。使用新的 - 功能更强大的解决方案：公用表格表达式 [ ^ ]。



有关CTE的更多信息，请参阅 Cristian Graus文章 [ ^ ]。



我差点忘了。请参阅 OriginalGriff解决方案 [ ^ ]与用户定义了用于拆分逗号分隔值的函数。

Not sure what you mean by saying "second parameter not working"... However, it's not problem.

The problem is somewhere else. Using WHILE loop is obsolete. Use new - more powerful - solution: Common Table Expressions[^].

More about CTE you'll find in Cristian Graus articles[^].

I almost forgot. Please see OriginalGriff solution[^] with user defined function to split comma separated values.

这篇关于逗号分隔符在程序中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！