34,588
社区成员
发帖
与我相关
我的任务
分享
declare @t table(T1 varchar(10),T2 varchar(10))
declare @ID varchar(max)
declare @sql varchar(max)
set @ID='A,1;A,2;B,1;B,3;C,6;D,12;'
select @sql='select '''+REPLACE(REPLACE(SUBSTRING(@ID,1,LEN(@ID)-1),',',''','''),';',''' union all select ''')+''''
insert into @t exec(@sql)
ALTER PROCEDURE [dbo].[OrderSelect]
@orderId nvarchar(30)
AS
BEGIN
SELECT *
from Z_OrderBOM where OrderId IN(
SELECT
--LEFT(item,CHARINDEX(',', item)-1) AS C1,
RIGHT(item,LEN(item)-CHARINDEX(',', item)) AS C2
FROM dbo.[Fun_String2ToStringArray](@s,';')
)
END
不太明白你的参数为什么要组合两个值进来。
一般传一串 id 值进来
ALTER PROCEDURE [dbo].[OrderSelect]
@orderId nvarchar(30)
AS
BEGIN
SELECT *
from Z_OrderBOM where OrderId=@orderId order by Name asc
END
比如这个就是存储过程,你给我语句如何添加进去呢?--添加分割表值函数
IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL
DROP FUNCTION [dbo].[Fun_String2ToStringArray]
GO
CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR(MAX), @split NVARCHAR(10))
RETURNS @table TABLE ([item] NVARCHAR(max))
AS
BEGIN
IF LEN(@split) = 0
BEGIN
SET @split = N','
END
DECLARE @xml XML;
SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>')
INSERT INTO @table
SELECT item
FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item]
FROM @xml.nodes('/x') t(c)) t
WHERE item IS NOT NULL
RETURN
END
GO
--
DECLARE @s NVARCHAR(MAX)
SET @s ='A,1;A,2;B,1;B,3;';
SELECT
LEFT(item,CHARINDEX(',', item)-1) AS C1
,RIGHT(item,LEN(item)-CHARINDEX(',', item)) AS C2
FROM dbo.[Fun_String2ToStringArray](@s,';')
/*
C1 C2
A 1
A 2
B 1
B 3
*/