Error in Store Procedure with cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error in Store Procedure with cursor

Author

Topic

hiteshj78
Starting Member

6 Posts

Posted - 2008-11-11 : 07:30:00

Hi my SP is

Error is

----------
catch me
Msg 266, Level 16, State 2, Procedure sp_Create_Task_Shortlisting_Demo, Line 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
--------------

Store Procedure is
------------
ALTER PROCEDURE [kk_admin].[sp_Create_Task_Shortlisting_Demo]
-- Add the parameters for the stored procedure here
@Task_Name varchar(50),
@TaskType_Id int

AS
SET NOCOUNT ON
Declare @Task_Date varchar(20)
Declare @Client_RegNo varchar(12)
Declare @CurrReq_Id int
Declare @Category_Id int
Declare @CurrReqCtg_Id int
Declare @Task_Desc varchar(8000)
Declare @Task_Status int
Declare @Task_AssignBy int
set @Task_AssignBy = 1

CREATE TABLE #Tbl(Task_Name varchar(50),Task_Date varchar(20),TaskType_Id int,Client_RegNo varchar(20),CurrReq_Id int,Category_Id int, CurrReqCtg_Id int, Task_Desc varchar(8000),Task_Status int)
INSERT INTO #Tbl Select @Task_Name,'1 nov 2006',@TaskType_Id,'234',1,1,1,'desc',1

DECLARE @TaskCursor CURSOR
SET @TaskCursor = CURSOR FOR
Select Task_Name, Task_Date, TaskType_Id, Client_RegNo, CurrReq_Id, Category_Id, CurrReqCtg_Id, Task_Desc,Task_Status from #Tbl
begin tran
OPEN @TaskCursor

FETCH NEXT FROM @TaskCursor
INTO @Task_Name, @Task_Date, @TaskType_Id, @Client_RegNo, @CurrReq_Id, @Category_Id, @CurrReqCtg_Id, @Task_Desc, @Task_Status

WHILE @@FETCH_STATUS = 0
BEGIN
print 'catch me'
FETCH NEXT FROM @TaskCursor
INTO @Task_Name, @Task_Date, @TaskType_Id, @Client_RegNo, @CurrReq_Id, @Category_Id, @CurrReqCtg_Id, @Task_Desc, @Task_Status
END
Select * from #Tbl
CLOSE @TaskCursor
DEALLOCATE @TaskCursor
---------------------------------

While executing find Error.....
Msg 266, Level 16, State 2, Procedure sp_Create_Task_Shortlisting_Demo, Line 1
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-11 : 07:49:44

one post for single topic is enough, See your last post

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-11 : 10:52:49

You have began tran and not commited it.

Place a "Commit" at end of procedure.

You should consider not using CURSOR for performance reasons.
Post real procedure to remove cursor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources