如何在Spql server 2005中将splitted string作为参数传递 [英] How to pass splitted string as parameter in Sp sql server 2005

问题描述

嗨,,

我需要将分割后的字符串作为参数传递给SQL存储过程请找到确切的解决方案....下面我的我的Sp请指导我实现我的解决方案...

你好，在我的表中我有一个名为肤色的列，其中我的数据为2,5，我得到两个和5个单独我需要传递2 nad 5的值和我应该在我的数据集中得到两行...但是我得到两行，两个值都是2,5，而不是2& 5单独

[代码] <前lang =SQL> DECLARE @ Test VARCHAR （ 1000 ）， @ RecSet VARCHAR （ 500 ）， @ ID VARCHAR （ 10 ）， @ Value < span class =code-keyword> VARCHAR （ 60 ）

DECLARE @ StartingPos INT ， @ RecPos INT

SET @ StartingPos = 1

SET @ Test =（<跨度 class =code-keyword>选择 cmp.complexion1 来自 customerexpectation cmp 其中 cmp。 customerid = ' 68572b93-16bd-4764-b75f-3aad35fef04b'）

WHILE @ StartingPos< = LEN（ @ Test ）

BEGIN

SELECT @ RecPos = CHARINDEX（' ，'， @ Test ， @ StartingPos ）

SELECT @ RecSet = SUBSTRING（ @ Test ， @ StartingPos ，@ RecPos- @ StartingPos）

PRINT @ RecSe t

SELECT CC.CUSTOMERID，CC.COMPLEXION1，CM.ID FROM CUSTOMEREXPECTATION CC
LEFT OUTER JOIN
COMPLEXION CM
ON CC.COMPLEXION1=@RecSet WHERE CUSTOMERID = ' 68572b93-16bd-4764-b75f-3aad35fef04b'

SELECT @ StartingPos = @ RecPos + 1

END [/ CODE]

解决方案

查看这些文章

在Sql Server中拆分函数以将逗号分隔的字符串分解为表 [ ^ ]

SQL SERVER - UDF - 功能于将列表转换为表格 [ ^ ]

Hi,,
I need to pass the splitted string as a parameter in SQL Stored Procedure please find the exact solu....Below i've my Sp please guide me to achieve my solution...
Hi in my table i have an column called complexion where i've the data as 2,5, i am getting two and 5 seperately i need to pass thw value of 2 nad 5 and i should get the two rows in my dataset...but i am getting two rows with both values as 2,5, only not as 2 & 5 seperately
[CODE]

DECLARE @Test VARCHAR(1000), @RecSet VARCHAR(500), @ID VARCHAR(10), @Value VARCHAR(60)

DECLARE @StartingPos INT,@RecPos INT

SET @StartingPos= 1

SET @Test =(select cmp.complexion1 from customerexpectation cmp where cmp.customerid='68572b93-16bd-4764-b75f-3aad35fef04b')

WHILE @StartingPos<=LEN(@Test)

BEGIN

    SELECT @RecPos = CHARINDEX(',',@Test,@StartingPos)

    SELECT @RecSet=SUBSTRING(@Test,@StartingPos,@RecPos-@StartingPos)
    
	PRINT @RecSet

	SELECT CC.CUSTOMERID,CC.COMPLEXION1,CM.ID FROM CUSTOMEREXPECTATION CC
	LEFT OUTER JOIN
	COMPLEXION CM
	ON CC.COMPLEXION1=@RecSet	WHERE CUSTOMERID='68572b93-16bd-4764-b75f-3aad35fef04b'

	SELECT @StartingPos=@RecPos+1

END[/CODE] 

解决方案

Check these articles
Split Function in Sql Server to break Comma-Separated Strings into Table[^]
SQL SERVER – UDF – Function to Convert List to Table[^]

这篇关于如何在Spql server 2005中将splitted string作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！