问题描述
我有一个SQL Server过程，该过程将团队随机分为组(A，B，C，...).
我已经成功编译了它，但是当我运行它时，它抛出一个错误:无效的对象名"Beach_Group_Names". EXECUTE之后的事务计数指示BEGIN和COMMIT语句的数量不匹配.上一个计数= 0，当前计数= 1.
我不知道为什么...
首先:Beach_Group_Names是一个游标.
第二点:我已经计算了开始和结束，并且计数是相同的.开始交易和提交交易应处于同一级别.
有人帮我解决此错误吗?
错误出现在:
I have an SQL server procedure which draws randomly teams to groups (A,B,C,...).
I have compiled it successfuly, but when I run it, it throws an error: Invalid object name ''Beach_Group_Names''. Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
And I wonder why...
At first: Beach_Group_Names is a Cursor.
At second: I have counted begins and ends and the count was the same. Begin Transaction and Commit Transaction should be at the same level.
Does anyone help me solve this error?
Error at:
Set @Group_Name = (SELECT TOP (1) Group_Name FROM Beach_Group_Names ORDER BY NEWID())
整个代码:
Whole code:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Pepin z Hane
-- Create date:
-- Description:
-- =============================================
CREATE PROCEDURE Draw_Groups
-- Add the parameters for the stored procedure here
@Tour_ID int
AS
Declare Team_IDs Cursor for Select Team_ID from Registration where Tournament_ID = @Tour_ID and Group_Name = 'RP' order by Datum_Registrace
Declare Beach_Group_Names Cursor for Select Group_Name from Beach_Group where Tournament_ID = @Tour_ID and Group_Name not like 'R%'
Declare @Group_Counter int
Set @Group_Counter = (select count (Group_Name) from Beach_Group where Tournament_ID = @Tour_ID)-4
Declare @Team_ID int
Declare @Celkovy_Pocet_Tymu int
Set @Celkovy_Pocet_Tymu = (select count(*) from Registration where Tournament_ID = @Tour_ID and Group_Name = 'RP')
Declare @Pocet_Tymu_Ve_Skupine int
Declare @Group_Name Varchar (5)
Declare @Datum_Registrace Datetime
BEGIN
Begin try
begin transaction
open Team_IDs
Fetch NEXT from Team_IDs into @Team_ID
While @@Fetch_Status = 0
begin
Set @Group_Name = (SELECT TOP (1) Group_Name FROM Beach_Group_Names ORDER BY NEWID())
--
while @Pocet_Tymu_Ve_Skupine >= @Celkovy_Pocet_Tymu/@Group_Counter begin
if @Group_Name = 'A' begin
Set @Group_Name = 'B'
end
else if @Group_Name = 'B' begin
if @Group_Counter > 2 begin
Set @Group_Name = 'C'
end
else begin
Set @Group_Name = 'A'
end
end
else if @Group_Name = 'C' begin
if @Group_Counter > 3 begin
Set @Group_Name = 'D'
end
else begin
Set @Group_Name = 'A'
end
end
else if @Group_Name = 'D' begin
if @Group_Counter > 4 begin
Set @Group_Name = 'E'
end
else begin
Set @Group_Name = 'A'
end
end
else if @Group_Name = 'E' begin
if @Group_Counter > 5 begin
Set @Group_Name = 'F'
end
else begin
Set @Group_Name = 'A'
end
end
else if @Group_Name = 'F' begin
Set @Group_Name = 'A'
end
end
--
Set @Datum_Registrace = (select Datum_Registrace from Registration where Tournament_ID = @Tour_ID and Team_ID = @Team_ID)
Insert into Registration values (@Tour_ID, @Group_Name, @Team_ID, 1, @Datum_Registrace, 0,0,0,0,0,0,0,0)
end
commit transaction
return 1
End Try
begin Catch
rollback
return 0
end Catch
END
GO
谢谢！
-Pepin zHané
Thanks!
-Pepin z Hané
推荐答案
Set @Group_Name = (SELECT TOP (1) Group_Name FROM Beach_Group_Names ORDER BY NEWID())
您需要打开Beach_Group_Names游标并将其包含在FETCH NEXT中，如下所示:
You need to open Beach_Group_Names cursor and include it to the FETCH NEXT, like this:
Fetch NEXT from Beach_Group_Names into @Group_Name
我看不到您在哪里打开Beach_Group_Names游标，所以我猜应该在顶部游标Team_IDs中打开它.
I don''t see where you open the Beach_Group_Names cursor, so I guess it should be opened inside the top cursor Team_IDs
这篇关于无效的对象名称"Beach_Group_Names". EXECUTE之后的事务计数指示BEGIN和COMMIT语句的数量不匹配.上一个计数= 0，当前计数= 1.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！