一个很复杂SQL存储过程的编写

1. IFEXISTS(SELECT*FROMsysobjectsWHEREtype='P'ANDname='RoleMenu_ADD')
2. BEGIN
3. PRINT'DroppingProcedureRoleMenu_ADD'
4. DROPProcedureRoleMenu_ADD
5. END
7. GO
9. PRINT'CreatingProcedureRoleMenu_ADD'
10. GO
12. CREATEProcedureRoleMenu_ADD
13. /*ParamList*/
14. @RoleIDint,
15. @MenuIDvarchar(5000)
16. AS
17. begin
19. --declare@RoleIDint,@MenuIDvarchar(5000)
20. --set@MenuID=',12,13,14,15'
21. --set@RoleID=5
22. createtable#allid(allidint)
23. --select*from#allID
24. createtable#familyTree(theidint)
25. declare@idsvarchar(4000),@strvarchar(4000)
26. set@ids=@MenuID--得到ID字符串
28. set@str=@ids
29. set@str=left(@str,len(@str)-1)
30. set@str=replace(@str,',','''aunionallselect''')
31. exec('insert#Familytree(theid)select'''+@str+'''')
33. DECLARE@idint,@id2int,@id3int
35. DECLAREmycursor2CURSORFOR
36. selecttheidfrom#Familytree
37. OPENmycursor2
38. FETCHNEXTFROMmycursor2
39. INTO@id
40. WHILE@@FETCH_STATUS=0
41. BEGIN
44. select@id2=MenuParentIdfromadminMenuwhereAMID=@id
45. insert#allid(allid)select@id
46. --select*fromadminMenu
48. while@id2>0
49. begin
50. insert#allid(allid)select@id2
51. Select@id3=MenuParentIdfromadminMenuwhereAMID=@id2
52. set@id2=@id3
53. continue
54. end
57. FETCHNEXTFROMmycursor2
58. INTO@id
59. END
60. CLOSEmycursor2
61. DEALLOCATEmycursor2
63. droptable#FamilyTree
65. DECLARE@allidstrvarchar(4000),@id1int
66. set@allidstr=''
67. DECLAREmycursor1CURSORFOR
68. selectdistinct(allid)from#allidorderbyallid
69. OPENmycursor1
70. FETCHNEXTFROMmycursor1
71. INTO@id1
72. WHILE@@FETCH_STATUS=0
73. BEGIN
75. set@allidstr=@allidstr+convert(varchar(10),@id1)+','
77. FETCHNEXTFROMmycursor1
78. INTO@id1
79. END
80. CLOSEmycursor1
81. DEALLOCATEmycursor1
83. droptable#allid
85. createtable#table2
86. (
87. theIDint
88. )
89. --select*from#table2
90. declare@str22varchar(5000)
91. set@str22=@allidstr
92. --set@str=left(@str,len(@str)-1)
93. set@str22=replace(@str22,',','''aunionallselect''')
94. exec('insert#table2(theid)select'''+@str22+'''')
96. BEGINTRANSACTION--开始事务
97. DECLARE@errorSunINT--定义错误计数器
98. SET@errorSun=0--没错为0
100. DECLARE@id111int
101. --set@allidstr=''
102. DECLAREmycursor111CURSORFOR
103. selectdistinct(theID)from#table2orderbytheID
104. OPENmycursor111
105. FETCHNEXTFROMmycursor111
106. INTO@id111
107. WHILE@@FETCH_STATUS=0
108. BEGIN
110. --set@allidstr=@allidstr+convert(varchar(10),@id1)+','
111. insertintoroleMenuvalues(@RoleID,@id111)
112. SET@errorSun=@errorSun+@@ERROR--累计是否有错
114. FETCHNEXTFROMmycursor111
115. INTO@id111
116. END
117. CLOSEmycursor111
118. DEALLOCATEmycursor111
120. IF@errorSun<>0
121. BEGIN
122. --PRINT'有错误，回滚'
123. ROLLBACKTRANSACTION--事务回滚语句
124. END
125. ELSE
126. BEGIN
127. --PRINT'成功，提交'
128. COMMITTRANSACTION--事务提交语句
129. END
130. --select*from#table2
131. droptable#table2
132. end
136. GO