Sql-Server
執行多個程序的問題
我對 SQL Server 開發非常陌生，而且我每天都在學習。
今天我在一個查詢中執行多個儲存過程時遇到了一個問題。我只看到兩者都開始了。經過長時間的研究，我可以看到查詢在執行第一個查詢後停止。
這是我的例子：
begin update Web.dbo._CoinPVPRegistered set Points=Points+1, [Last Kill]=@1target, [Date]=GETDATE(), pvp = pvp + 1 where CharID=@1killerid insert Web.dbo.PlusNotice(Sent, Message)values(0,@1killername + ' has killed ' + @1target + ' in PVP!') Exec Web.dbo._Achivement @1killerid,'18','0','0' Exec Web.dbo._Achivement @1targetID,'19','0','0' end系統在剛剛結束後停止執行此查詢
Exec Web.dbo._Achivement @1killerid,'18','0','0'執行第一個後，下一個不會啟動。
你有什麼想法還是我做錯了什麼？
感謝您的任何幫助
編輯：基本程序
_AchievementUSE [Web] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[_Achivement] @CharID int, @EventID int, @ItemID int, @strDesc nvarchar(max) as BEGIN If @EventID = 18 Begin update web.dbo._Achievement set win = win +1 where CharID = @CharID end If @EventID = 19 Begin update web.dbo._Achievement set fail = fail +1 where CharID = @CharID end end編輯2：
完成程序：
USE [Web] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER PROCEDURE [dbo].[_CoinpvpregisteredSP] @CharID int, @EventID tinyint, @Data1 int, @Data2 int, @strPos varchar(64), @Desc varchar(128) as BEGIN declare @1strip varchar(120) declare @1killername varchar(120) declare @1killerid int declare @1target varchar(12) declare @1targetID int declare @Date datetime declare @1targettime datetime declare @2targettime datetime declare @lvl nvarchar (max) declare @Dlvl nvarchar (max) set @1strip = REPLACE(@Desc,'[My: no job, Neutral, Kirin] [His(', '') set @1killername = REPLACE(@1strip,'): no job, Neutral, Kirin]', '') set @1killerid = (select CharID from _Char where CharName16=@1killername) set @1target = (select CharName16 from _Char where CharID=@CharID) set @1targetID = (select CharID from _char where Charname16 = @1target) Set @Date = GetDate() declare @IP1 varchar(64) = (select ip from _CharIP Where Charname = @1killername) declare @IP2 varchar(64) = (select ip from _CharIP Where Charname = @1target) Print @IP1 Print @IP2 IF (@IP1 = @IP2) begin print 'sameip' Insert into _CheatTable (CharID,Reason,Date) Values (@1killerid,'Trying to Cheat on pvp mode by ip',GETDATE()) end else begin Set @lvl = (Select CurLevel from _Char where CharID=@1killerid) Set @Dlvl = (Select CurLevel from _Char where CharID=@CharID) print @lvl print @Dlvl If @lvl = @Dlvl begin Set @1targettime = (select Date from _CoinPVPRegistered where CharID = @1killerid) Set @2targettime = dateadd(minute, -1, getdate()) If @1targettime >= @2targettime begin Insert into _CheaterTable (CharID,Reason,Date) Values (@1killerid,'Trying to Cheat on pvp mode coin',GETDATE()) end else begin update _CoinPVPRegistered set Points=Points+1, [Last Kill]=@1target, [Date]=GETDATE(), pvp = pvp + 1 where CharID=@1killerid insert PlusNotice(Sent, Message)values(0,@1killername + ' has killed ' + @1target + ' in PVP!') --print @1killerid --print @1targetID Exec _Achivement @1killerid,18,0,'0' Exec _Achivement @1targetID,19,0,'0' end end end end原始資訊來自表格由外部軟體所以這是一個儲存過程誰開始執行
由於這個過程是自動化的 The Procedure Stop at the second exec (exec _achievement @1targetID,19,0,‘0’) 她不執行這部分
但是，如果我自己執行程序 Whit the Value 一切正常
USE [Web] GO DECLARE @return_value int EXEC @return_value = [dbo].[_CoinpvpregisteredSP] @CharID = 85307, @EventID = 20, @Data1 = 0, @Data2 = 0, @strPos = N'0', @Desc = N'[My: no job, Neutral, Kirin] [His(No_Maresy): no job, Neutral, Kirin]' SELECT 'Return Value' = @return_value GO結果 Whit 手動啟動 = 成功
41.47.240.109 5.107.179.65 140 140 (1 row(s) affected) 90086 85307 event18 (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) Event 19 (1 row(s) affected) (1 row(s) affected) (1 row(s) affected) (1 row(s) affected)我怎麼知道她沒有像自動化一樣工作那麼簡單為了確保我添加了一個新表
USE [Web] GO /****** Object: Table [dbo].[_SystemEventConfirmation] Script Date: 12/23/2015 11:21:07 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[_SystemEventConfirmation]( [CharID] [int] NULL, [Charname] [nvarchar](max) NULL, [EventID] [int] NULL, [Date] [datetime] NOT NULL, CONSTRAINT [PK__SystemEventConfirmation] PRIMARY KEY CLUSTERED ( [Date] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO將表放在_Achievement 過程的末尾以重新檢查數據
Insert into Web.dbo._SystemEventConfirmation (CharID,Charname,EventId,Date) values (@CharID,@Charname,@EventID,GetDate())Whit 自動化系統 結果是
85307 SpOokShoW 18 2015-12-22 19:55:23.397使用程序手動發送完全相同的數據
85307 No_Maresy 19 2015-12-22 19:57:15.123 90086 SpOokShoW 18 2015-12-22 19:57:15.020我完全迷失了
為什麼這應該有效而手動執行它而不是發送完全相同數據的軟體上的自動功能
此範例將起作用，也許您想在此基礎上繼續：
CREATE PROCEDURE [dbo].[_Achivement_test] @CharID int, @EventID int, @ItemID int, @strDesc nvarchar(max) as BEGIN If @EventID = 18 Begin print 'win part' --update web.dbo._Achievement set win = win +1 where CharID = @CharID end If @EventID = 19 Begin print 'fail part' -- update web.dbo._Achievement set fail = fail +1 where CharID = @CharID end if @EventID <> 18 and @EventID <> 19 begin print 'Not implemented exception part' end end GO --TEST: Exec [dbo].[_Achivement_test] 1,18,0,'0' Exec [dbo].[_Achivement_test] 1,19,0,'0' Exec [dbo].[_Achivement_test] 1,99,0,'0'