- IFEXISTS(SELECT*FROMsysobjectsWHEREtype='P'ANDname='RoleMenu_ADD')
- BEGIN
- PRINT'DroppingProcedureRoleMenu_ADD'
- DROPProcedureRoleMenu_ADD
- END
- GO
- PRINT'CreatingProcedureRoleMenu_ADD'
- GO
- CREATEProcedureRoleMenu_ADD
- /*ParamList*/
- @RoleIDint,
- @MenuIDvarchar(5000)
- AS
- begin
- --declare@RoleIDint,@MenuIDvarchar(5000)
- --set@MenuID=',12,13,14,15'
- --set@RoleID=5
- createtable#allid(allidint)
- --select*from#allID
- createtable#familyTree(theidint)
- declare@idsvarchar(4000),@strvarchar(4000)
- set@ids=@MenuID--得到ID字符串
- set@str=@ids
- set@str=left(@str,len(@str)-1)
- set@str=replace(@str,',','''aunionallselect''')
- exec('insert#Familytree(theid)select'''+@str+'''')
- DECLARE@idint,@id2int,@id3int
- DECLAREmycursor2CURSORFOR
- selecttheidfrom#Familytree
- OPENmycursor2
- FETCHNEXTFROMmycursor2
- INTO@id
- WHILE@@FETCH_STATUS=0
- BEGIN
- select@id2=MenuParentIdfromadminMenuwhereAMID=@id
- insert#allid(allid)select@id
- --select*fromadminMenu
- while@id2>0
- begin
- insert#allid(allid)select@id2
- Select@id3=MenuParentIdfromadminMenuwhereAMID=@id2
- set@id2=@id3
- continue
- end
- FETCHNEXTFROMmycursor2
- INTO@id
- END
- CLOSEmycursor2
- DEALLOCATEmycursor2
- droptable#FamilyTree
- DECLARE@allidstrvarchar(4000),@id1int
- set@allidstr=''
- DECLAREmycursor1CURSORFOR
- selectdistinct(allid)from#allidorderbyallid
- OPENmycursor1
- FETCHNEXTFROMmycursor1
- INTO@id1
- WHILE@@FETCH_STATUS=0
- BEGIN
- set@allidstr=@allidstr+convert(varchar(10),@id1)+','
- FETCHNEXTFROMmycursor1
- INTO@id1
- END
- CLOSEmycursor1
- DEALLOCATEmycursor1
- droptable#allid
- createtable#table2
- (
- theIDint
- )
- --select*from#table2
- declare@str22varchar(5000)
- set@str22=@allidstr
- --set@str=left(@str,len(@str)-1)
- set@str22=replace(@str22,',','''aunionallselect''')
- exec('insert#table2(theid)select'''+@str22+'''')
- BEGINTRANSACTION--开始事务
- DECLARE@errorSunINT--定义错误计数器
- SET@errorSun=0--没错为0
- DECLARE@id111int
- --set@allidstr=''
- DECLAREmycursor111CURSORFOR
- selectdistinct(theID)from#table2orderbytheID
- OPENmycursor111
- FETCHNEXTFROMmycursor111
- INTO@id111
- WHILE@@FETCH_STATUS=0
- BEGIN
- --set@allidstr=@allidstr+convert(varchar(10),@id1)+','
- insertintoroleMenuvalues(@RoleID,@id111)
- SET@errorSun=@errorSun+@@ERROR--累计是否有错
- FETCHNEXTFROMmycursor111
- INTO@id111
- END
- CLOSEmycursor111
- DEALLOCATEmycursor111
- IF@errorSun<>0
- BEGIN
- --PRINT'有错误,回滚'
- ROLLBACKTRANSACTION--事务回滚语句
- END
- ELSE
- BEGIN
- --PRINT'成功,提交'
- COMMITTRANSACTION--事务提交语句
- END
- --select*from#table2
- droptable#table2
- end
- GO
一个很复杂SQL存储过程的编写
最新推荐文章于 2022-10-12 20:13:54 发布