SQL创建存储过程

创建存储过程

创建存储过程sppbConvertStringToTable，实现数据的拆分,使以下代码得以正常运行，运行结果如下图

构建测试数据

 SELECT uGuid='A',sOrderNO='AAA',sWorkerNo='001,002,003' INTO #TEMP
    INSERT INTO #TEMP VALUES('B','BBB','002,004')
    INSERT INTO #TEMP VALUES('C','CCC','') --测试空值

开始测试

选择测试1

SELECT sGroup=uGuid,sNo=sWorkerNo,iCount=Convert(integer,0)
INTO #StringTable
FROM #TEMP

选择测试2

EXEC dbo.sppbConvertStringToTable ',',1

选择测试3

SELECT * FROM #TEMP ORDER BY uGuid

选择测试4

SELECT A.uGuid,A.sOrderNo
,B.sNo,B.iCount
FROM #TEMP A
INNER JOIN #StringTable B ON B.sGroup=A.uGuid
ORDER BY B.sGroup

选择测试5

DROP TABLE #StringTable

测试完成

DROP TABLE #TEMP