百度网站安全检测,网站建设公众号开,上海网站建设求职简历,在线图片编辑器马赛克汗颜#xff0c;做了这么多年开发自己第一次用游标和临时表 还是借助度娘才写出来的#xff0c;请大家给指点下。。。 1 CREATE PROCEDURE [dbo].[sp_LaodDefaultFM]2 (3 ExhID int ,4 DefaultExhID INT,5 Result INT6 )7 AS 8 BEGIN 9 --判断当前会话中临时表是… 汗颜做了这么多年开发自己第一次用游标和临时表 还是借助度娘才写出来的请大家给指点下。。。 1 CREATE PROCEDURE [dbo].[sp_LaodDefaultFM]2 (3 ExhID int ,4 DefaultExhID INT,5 Result INT6 )7 AS 8 BEGIN 9 --判断当前会话中临时表是否存在
10 if exists(select * from dbo.sysobjects where object_id(tempdb.dbo.#temp) is not null)
11 DROP TABLE #temp
12 ELSE
13 CREATE TABLE #temp(FsvID INT, FskID INT, FsvValue NVARCHAR(500) )
14 if exists(select * from dbo.sysobjects where object_id(tempdb.dbo.#temp2) is not null)
15 delete from #temp2
16 declare defaultfsv int
17 INSERT INTO #temp SELECT FsvID,FskID,FsvValue FROM dbo.FuncSetValue WHERE ExhIDDefaultExhID
18
19 --开始事务
20 BEGIN TRAN
21 DECLARE error INT
22 declare suberror INT
23 SET error0 SET suberror0
24 --声明游标
25 DECLARE fsvindex CURSOR FOR SELECT fsvid FROM #temp
26 --打开游标
27 OPEN fsvindex
28 WHILE FETCH_STATUS0
29
30 BEGIN
31 --开始循环游标变量
32 FETCH NEXT FROM fsvindex INTO defaultfsv
33 --执行操作
34 DECLARE curfsvid INT
35 INSERT dbo.FuncSetValue( FskID ,FsvValue ,FsvCreatetime ,ExhID )( SELECT FskID,FsvValue,GETDATE(),ExhID FROM dbo.FuncSetValue WHERE FsvIDdefaultfsv)
36 SET curfsvid(SELECT IDENTITY)
37 IF curfsvid0
38 BEGIN
39 INSERT dbo.FuncSetSubValue( FssID ,FsvID ,FbvKey ,FbvValue ,FbvCreatime ,FbvOrderStr)( SELECT FssID,curfsvid,FbvKey,FbvValue,GETDATE(),0 FROM dbo.FuncSetSubValue WHERE FsvIDdefaultfsv)SET suberrorsuberrorERROR
40 END
41 ELSE
42 BEGIN
43 SET errorerror1
44 END
45 END
46
47
48 IF error0 AND suberror0
49 BEGIN
50 SET Result1
51 COMMIT TRAN
52 END
53 ELSE
54 BEGIN
55 SET Result0
56 ROLLBACK TRAN
57 END
58 --关闭游标
59 CLOSE fsvindex
60 --释放游标
61 DEALLOCATE fsvindex
62 SELECT Result
63
64
65 END
66
67
68
69
70
71 GO Code 转载于:https://www.cnblogs.com/qzzy/p/8168658.html
