本文介绍了如何使用SQL Server 2000在sp中提取逗号分隔的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

你好朋友,
我有像国家一样的表名.

Hello friends,
I have Table name as country like.

Id    CountryName
1     India
2     Aus
3     Pak
4     US
5     Eng



我的存储过程就像.



And my stored procedure is like.

CREATE PROCEDURE getCountry
@Id(VARCHAR 10)
AS
BEGIN
      IF @Id IS NOT NULL
         SELECT CountryName FROM Country where Id = @Id
      END
END
GO



我正在以字符串格式将参数作为Id = 1,2,3,4,5发送,



I am sending the parameter as Id = 1,2,3,4,5 in string format,
How can we extract the comma separated?

推荐答案

declare  @roleIds varchar(250),
    @userId int
BEGIN
 set @roleIds=''1,2,3,4,5,6,7,''
DECLARE @sItem VARCHAR(8000)
While (Charindex('','',@roleIds)>0)
    Begin
        Select  @sItem = ltrim(rtrim(Substring(@roleIds,1,Charindex('','',@roleIds)-1)))
        Set @roleIds = Substring(@roleIds,Charindex('','',@roleIds)+1,len(@roleIds))
        declare @n int
        SET @n = CONVERT(int, @sItem)
        print @n
    End
END





希望对您有帮助....





I hope this may help you....


CREATE FUNCTION fn_Split_Up_Ids
(
   @Param_Ids varchar(500)
)
RETURNS @Id_Table TABLE(IDField int)

AS
BEGIN  
   IF (LEN(@Param_Ids) <= 0) 
      RETURN

   DECLARE @CommaPos smallint
   SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))	  

   IF @CommaPos = 0
       INSERT INTO @Id_Table 
              VALUES(CONVERT(BIGINT ,RTRIM(LTRIM(@Param_Ids))))
   ELSE 
       BEGIN
           WHILE LEN(@Param_Ids) > 1
	   BEGIN
	     SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
             INSERT INTO @Id_Table 
                      VALUES(CONVERT(INT ,SUBSTRING(RTRIM(LTRIM(@Param_Ids)),1, @CommaPos - 1)))
	     SET @Param_Ids = SUBSTRING(RTRIM(LTRIM(@Param_Ids)), @CommaPos + 1 , LEN(RTRIM(LTRIM(@Param_Ids))))
	     SET @CommaPos = CHARINDEX(',', RTRIM(LTRIM(@Param_Ids)))
	     IF @CommaPos = 0
	     BEGIN
                 INSERT INTO @Id_Table VALUES(CONVERT(INT ,RTRIM(LTRIM(@Param_Ids))))
                 BREAK
	     END
	   END
       END
       RETURN 
  END




然后您需要如下修改SP:




AND THEN YOU NEED TO MODIFY YOUR SP AS BELOW:

CREATE PROCEDURE spGetCountry
@Id VARCHAR(100)
AS
BEGIN
         SELECT Country FROM tblCountry where Id IN (select * from fn_Split_Up_Ids(@Id))
END
GO



您在这里:)
现在拨打您的SP



HERE YOU GO :)
NOW CALL YOUR SP

EXEC spGetCountry '1,2'



这篇关于如何使用SQL Server 2000在sp中提取逗号分隔的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-31 08:32