22,210
社区成员
发帖
与我相关
我的任务
分享史上最奇葩的，最难解的SQL Server 疑难杂症
create FUNCTION [dbo].[fn_TempObjectStringsByInterval]
(
@ObjectStrings VARCHAR(max) ,
@strInterval CHAR(1)
)
RETURNS @TempObjectID TABLE
(
ID_Num111 INT IDENTITY(1, 1) ,
ObjectString VARCHAR(max)
)
AS
BEGIN
DECLARE @ObjectString VARCHAR(max)
DECLARE @NowIndex INT
DECLARE @PreIndex INT
IF SUBSTRING(@ObjectStrings, LEN(@ObjectStrings), 1) <> @strInterval
SET @ObjectStrings = @ObjectStrings + @strInterval
SET @NowIndex = CHARINDEX(@strInterval, @ObjectStrings)
SET @PreIndex = 1
WHILE @NowIndex > 0
BEGIN
SET @ObjectString = SUBSTRING(@ObjectStrings, @PreIndex,
@NowIndex - @PreIndex)
INSERT INTO @TempObjectID
( ObjectString )
VALUES ( @ObjectString )
SET @PreIndex = @NowIndex + 1
SET @NowIndex = CHARINDEX(@strInterval, @ObjectStrings,
@PreIndex)
END
RETURN
END
GO
create PROCEDURE [dbo].test2
(
@CostDiff VARCHAR(max) = ''
)
AS
DECLARE @Count AS INT
SELECT IDENTITY( INT, 1,1 ) AS ID_Num ,
tb.ObjectSTring
INTO #temp_CostDiff111
FROM dbo.fn_TempObjectStringsByInterval(@CostDiff, ';') tb
DECLARE @I VARCHAR(max)
SELECT @I = ObjectSTring
FROM #temp_CostDiff111
WHERE ID_Num = 1
PRINT @I
DROP TABLE #temp_CostDiff111 ;
GO
create PROCEDURE [dbo].test3
(
@CostDiff VARCHAR(max) = ''
)
AS
SELECT *
INTO #temp_CostDiff2
FROM dbo.fn_TempObjectStringsByInterval(@CostDiff, ';')
DROP TABLE #temp_CostDiff2
GO
create PROCEDURE [dbo].[test1]
(
@BudgetList VARCHAR(MAX)
)
AS
DECLARE @BudgetListOfOne VARCHAR(MAX)
DECLARE @Count AS INT
BEGIN TRAN
SELECT ID_Num111 AS ID_Num ,
ObjectSTring
INTO #temp_BudgetList
FROM dbo.fn_TempObjectStringsByInterval(@BudgetList, '|')
SELECT @Count = COUNT(*)
FROM #temp_BudgetList
WHILE @Count > 0
BEGIN
SELECT @BudgetListOfOne = ObjectSTring
FROM #temp_BudgetList
WHERE ID_Num = @Count ;
EXEC test2 @BudgetListOfOne
DECLARE @dd VARCHAR(MAX)
SET @dd = @BudgetListOfOne
EXEC test3 @dd
SET @count = @count - 1
END
DROP TABLE #temp_BudgetList
IF @@error <> 0
BEGIN
ROLLBACK TRAN
END
COMMIT TRAN
GO
---------------------------------------------------------------------------
SET NOCOUNT ON
EXEC test1
@BudgetList = 'aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4'
GO
--为什么结果中有一个与众不同列？
为什么？能解答一下吗？？不要修改其中的逻辑，我只求为什么这里会有一条记录与众不同？？？
(
@ObjectStrings VARCHAR(max) ,
@strInterval CHAR(1)
)
RETURNS @TempObjectID TABLE
(
ID_Num111 INT IDENTITY(1, 1) ,
ObjectString VARCHAR(max)
)
AS
BEGIN
DECLARE @ObjectString VARCHAR(max)
DECLARE @NowIndex INT
DECLARE @PreIndex INT
IF SUBSTRING(@ObjectStrings, LEN(@ObjectStrings), 1) <> @strInterval
SET @ObjectStrings = @ObjectStrings + @strInterval
SET @NowIndex = CHARINDEX(@strInterval, @ObjectStrings)
SET @PreIndex = 1
WHILE @NowIndex > 0
BEGIN
SET @ObjectString = SUBSTRING(@ObjectStrings, @PreIndex,
@NowIndex - @PreIndex)
INSERT INTO @TempObjectID
( ObjectString )
VALUES ( @ObjectString )
SET @PreIndex = @NowIndex + 1
SET @NowIndex = CHARINDEX(@strInterval, @ObjectStrings,
@PreIndex)
END
RETURN
END
GO
create PROCEDURE [dbo].test2
(
@CostDiff VARCHAR(max) = ''
)
AS
DECLARE @Count AS INT
SELECT IDENTITY( INT, 1,1 ) AS ID_Num ,
tb.ObjectSTring
INTO #temp_CostDiff111
FROM dbo.fn_TempObjectStringsByInterval(@CostDiff, ';') tb
DECLARE @I VARCHAR(max)
SELECT @I = ObjectSTring
FROM #temp_CostDiff111
WHERE ID_Num = 1
PRINT @I
DROP TABLE #temp_CostDiff111 ;
GO
create PROCEDURE [dbo].test3
(
@CostDiff VARCHAR(max) = ''
)
AS
SELECT *
INTO #temp_CostDiff2
FROM dbo.fn_TempObjectStringsByInterval(@CostDiff, ';')
DROP TABLE #temp_CostDiff2
GO
create PROCEDURE [dbo].[test1]
(
@BudgetList VARCHAR(MAX)
)
AS
DECLARE @BudgetListOfOne VARCHAR(MAX)
DECLARE @Count AS INT
BEGIN TRAN
SELECT ID_Num111 AS ID_Num ,
ObjectSTring
INTO #temp_BudgetList
FROM dbo.fn_TempObjectStringsByInterval(@BudgetList, '|')
SELECT @Count = COUNT(*)
FROM #temp_BudgetList
WHILE @Count > 0
BEGIN
SELECT @BudgetListOfOne = ObjectSTring
FROM #temp_BudgetList
WHERE ID_Num = @Count ;
EXEC test2 @BudgetListOfOne
DECLARE @dd VARCHAR(MAX)
SET @dd = @BudgetListOfOne
EXEC test3 @dd
SET @count = @count - 1
END
DROP TABLE #temp_BudgetList
IF @@error <> 0
BEGIN
ROLLBACK TRAN
END
COMMIT TRAN
GO
---------------------------------------------------------------------------
SET NOCOUNT ON
EXEC test1
@BudgetList = 'aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4'
GO
--为什么结果中有一个与众不同列？
为什么？能解答一下吗？？不要修改其中的逻辑，我只求为什么这里会有一条记录与众不同？？？
...全文
请发表友善的回复…
发表回复
-晴天 2011-06-18
- 打赏
- 举报
declare @s varchar(max)
set @s='aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4'
select substring(@s,number,charindex('|',@s+'|',number+1)-number)s from master..spt_values
where type='p' and number<=len(@s) and substring(@s,number,1)<>'|' and substring('|'+@s,number,1)='|'
/*
s
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
(25 行受影响)
*/
claro 2011-06-17
- 打赏
- 举报
为标题而入，见8F而出。
FlySQL 2011-06-17
- 打赏
- 举报
如果分号的位置不固定
set @BudgetList='select '''+replace(@BudgetList,'|',''' as a union all select ''')+''''
exec ('select left(a,charindex('';'',a)-1) as col from ('+@BudgetList+') t')FlySQL 2011-06-17
- 打赏
- 举报
declare @BudgetList VARCHAR(MAX)
set @BudgetList = 'aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4'
set @BudgetList='select '''+replace(@BudgetList,'|',''' as a union all select ''')+''''
exec ('select left(a,36) as col from ('+@BudgetList+') t')
/**
col
------------------------------------------------------------------------
aaBB16B5-3213-47B4-84C0-D1033B7D87CB
bbBB16B5-3213-47B4-84C0-D1033B7D87CB
ccBB16B5-3213-47B4-84C0-D1033B7D87CB
ddBB16B5-3213-47B4-84C0-D1033B7D87CB
eeBB16B5-3213-47B4-84C0-D1033B7D87CB
ffBB16B5-3213-47B4-84C0-D1033B7D87CB
ggBB16B5-3213-47B4-84C0-D1033B7D87CB
hhBB16B5-3213-47B4-84C0-D1033B7D87CB
iiBB16B5-3213-47B4-84C0-D1033B7D87CB
jjBB16B5-3213-47B4-84C0-D1033B7D87CB
kkBB16B5-3213-47B4-84C0-D1033B7D87CB
llBB16B5-3213-47B4-84C0-D1033B7D87CB
mmBB16B5-3213-47B4-84C0-D1033B7D87CB
nnBB16B5-3213-47B4-84C0-D1033B7D87CB
00BB16B5-3213-47B4-84C0-D1033B7D87CB
ppBB16B5-3213-47B4-84C0-D1033B7D87CB
qqBB16B5-3213-47B4-84C0-D1033B7D87CB
rrBB16B5-3213-47B4-84C0-D1033B7D87CB
ssBB16B5-3213-47B4-84C0-D1033B7D87CB
ttBB16B5-3213-47B4-84C0-D1033B7D87CB
uuBB16B5-3213-47B4-84C0-D1033B7D87CB
vvBB16B5-3213-47B4-84C0-D1033B7D87CB
wwBB16B5-3213-47B4-84C0-D1033B7D87CB
xxBB16B5-3213-47B4-84C0-D1033B7D87CB
yyBB16B5-3213-47B4-84C0-D1033B7D87CB
**/
guohao0826 2011-06-17
- 打赏
- 举报
FlySQL说的对，运行那个脚本就知道参数的内容。很简单的
FlySQL 2011-06-17
- 打赏
- 举报
没什么耐心去看楼主的脚本，这个需求两句sql语句就可以搞定
set @BudgetList='select '''+replace(@BudgetList,'|',''' as a union all select ''')+''''
exec (@BudgetList)
GoAwayZ 2011-06-17
- 打赏
- 举报
数据乱的，看不出来多了什么。
FlySQL 2011-06-17
- 打赏
- 举报
declare @BudgetList VARCHAR(MAX)
set @BudgetList = 'aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4'
set @BudgetList='select '''+replace(@BudgetList,'|',''' as a union all select ''')+''''
exec (@BudgetList)
/**
a
-----------------------------------------------------------------------------------
aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
**/
set @BudgetList = 'aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4|yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4'
set @BudgetList='select '''+replace(@BudgetList,'|',''' as a union all select ''')+''''
exec (@BudgetList)
/**
a
-----------------------------------------------------------------------------------
aaBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
bbBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ccBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ddBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
eeBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ffBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ggBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
hhBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
iiBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
jjBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
kkBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
llBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
mmBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
nnBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
00BB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ppBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
qqBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
rrBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ssBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
ttBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
uuBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
vvBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
wwBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
xxBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
yyBB16B5-3213-47B4-84C0-D1033B7D87CB;NBYY.aaa.011111111111111111111111;A.01.01.01;4
**/
guohao0826 2011-06-17
- 打赏
- 举报
线索如下： 如果去掉test1中的begin tran 事务。则不会出现问题。
