用游标实现:
declare Cur_test cursor for
SELECT RULEID,SMTPON FROM HELPDESKRULE
open Cur_test
fetch next from Cur_test into @RULEID,@EMAILSENT
while @@fetch_status=0
begin
--写你要进行的操作
fetch next from Cur_test into @RULEID,@EMAILSENT
end
close Cur_test
deallocate Cur_test
declare Cur_test cursor for
SELECT RULEID,SMTPON FROM HELPDESKRULE
open Cur_test
fetch next from Cur_test into @RULEID,@EMAILSENT
while @@fetch_status=0
begin
--写你要进行的操作
fetch next from Cur_test into @RULEID,@EMAILSENT
end
close Cur_test
deallocate Cur_test
AS
SET NOCOUNT ON
DECLARE @MAXID INT,@SQL NVARCHAR(1000)
DECLARE @RULEID BIGINT,@CALLTIME DATETIME,@INCOMINGCALL TINYINT,@STATUS TINYINT,@RESPONSETIME SMALLINT,@LASTUPDATE DATETIME,@SMTPON BIT
DECLARE Type_Cursor CURSOR FOR
SELECT RULEID,SMTPON FROM HELPDESKRULE
OPEN Type_CursorSET @CALLTIME = GETDATE()
SET @LASTUPDATE = GETDATE()
SET @INCOMINGCALL = 0
SET @STATUS = 1
SET @RESPONSETIME = 1
FETCH NEXT FROM Type_Cursor INTO @RULEID,@SMTPON
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO CALL(CALLTIME,INCOMINGCALL,STATUS,RESPONSETIME,LASTUPDATE)
VALUES(@CALLTIME,@INCOMINGCALL,@STATUS,@RESPONSETIME,@LASTUPDATE)
SELECT @MAXID = SCOPE_IDENTITY()
CREATE TABLE #TMP1 (CALLIDX BIGINT,AIIDX BIGINT,EMAILSENT BIT) SELECT @SQL=GENERATEDSQL FROM HELPDESKRULE WHERE RULEID=@RULEID
INSERT INTO #TMP1(AIIDX)
EXEC(@SQL) UPDATE #TMP1 SET CALLIDX=@MAXID , EMAILSENT=@SMTPON
INSERT INTO CALLEXIST(CALLIDX,AIIDX,EMAILSENT)
SELECT CALLIDX,AIIDX,EMAILSENT FROM #TMP1
DROP TABLE #TMP1 FETCH NEXT FROM Type_Cursor into @RULEID,@SMTPON
END
CLOSE Type_Cursor
DEALLOCATE Type_Cursor
--SELECT @RULEID = RULEID,@EMAILSENT= SMTPON FROM HELPDESKRULE
GO
我这样好像ok了,测试先
AS
BEGIN
SET NOCOUNT ON
DECLARE @MAXID INT,@SQL NVARCHAR(1000)
DECLARE @RULEID BIGINT,@CALLTIME DATETIME,@INCOMINGCALL TINYINT,@STATUS TINYINT
DECLARE @RESPONSETIME SMALLINT,@LASTUPDATE DATETIME,@EMAILSENT BIT
SET @CALLTIME = GETDATE()
SET @LASTUPDATE = GETDATE()
SET @INCOMINGCALL = 0
SET @STATUS = 1
SET @RESPONSETIME = 1
DECLARE T_CURSOR CURSOR FOR
SELECT RULEID,SMTPON FROM HELPDESKRULE
OPEN T_CURSOR
FETCH NEXT FROM T_CURSOR INTO @RULEID,@EMAILSENT
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO CALL(CALLTIME,INCOMINGCALL,STATUS,RESPONSETIME,LASTUPDATE)
VALUES(@CALLTIME,@INCOMINGCALL,@STATUS,@RESPONSETIME,@LASTUPDATE)
SELECT @MAXID = SCOPE_IDENTITY()
CREATE TABLE #TMP1 (CALLIDX BIGINT,AIIDX BIGINT,EMAILSENT BIT)
SELECT @SQL=GENERATEDSQL FROM HELPDESKRULE WHERE RULEID=@RULEID
INSERT INTO #TMP1(AIIDX)
EXEC(@SQL)
UPDATE #TMP1 SET CALLIDX=@MAXID , EMAILSENT=@EMAILSENT
INSERT INTO CALLEXIST(CALLIDX,AIIDX,EMAILSENT)
SELECT CALLIDX,AIIDX,EMAILSENT FROM #TMP1
DROP TABLE #TMP1 FETCH NEXT FROM T_CURSOR INTO @RULEID,@EMAILSENT
END
CLOSE T_CURSOR
DEALLOCATE T_CURSOR
END
GO