本文介绍了如何使用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中提取逗号分隔的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!