Error in cursor with temp Table

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 cursor with temp Table

Author

Topic

hiteshj78
Starting Member

6 Posts

Posted - 2008-11-11 : 06:09:29

Hi,

I have one cursor, That have inner cursor. If i am executing this in query anylizer, cursor is return prerfect data.
But when i used with passing parameter in store procedure. It gives me error.

My cursor is.

-------------------------------------------------
ALTER PROCEDURE [kk_admin].[sp_Create_Task_Shortlisting]
-- 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
--DECLARE vendor_cursor CURSOR FOR
IF OBJECT_ID('TempDB..#Tbl','U') IS NOT NULL
drop table #Tbl

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 exec sp_Create_TaskList_Shortlisting @Task_Name,@TaskType_Id

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
Declare @AssignTo_Id int
IF OBJECT_ID('TempDB..#tmpTbl1','U') IS NOT NULL
drop table #tmpTbl1

CREATE TABLE #tmpTbl1(ID int)
INSERT INTO #tmpTbl1 exec KK_MASTER.kk_admin.sp_Get_AssignTo_ShortList
Select ID from #tmpTbl1

DECLARE @SubCursor CURSOR
SET @SubCursor = CURSOR FOR
SELECT ID FROM #tmpTbl1

OPEN @SubCursor
begin tran
FETCH NEXT FROM @SubCursor INTO @AssignTo_Id

WHILE @@FETCH_STATUS = 0
BEGIN

if not exists(Select Task_Id from KK_MASTER.kk_admin.Task_Master Where CurrReqCtg_Id = @CurrReqCtg_Id and Task_AssignTo = @AssignTo_Id and Convert(varchar(10),Task_AssignDate,104) = Convert(varchar(10),getdate(),104))begin
print 'test data'
end
FETCH NEXT FROM @SubCursor INTO @AssignTo_Id
END

drop table #tmpTbl1

CLOSE @SubCursor
DEALLOCATE @SubCursor

FETCH NEXT FROM @TaskCursor
INTO @Task_Name, @Task_Date, @TaskType_Id, @Client_RegNo, @CurrReq_Id, @Category_Id, @CurrReqCtg_Id, @Task_Desc, @Task_Status
END
drop table #Tbl

CLOSE @TaskCursor
DEALLOCATE @TaskCursor
----------------------------------------------------

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-11 : 06:42:41

What was the error it generated ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 06:45:07

can you explain your requirement? i think it can be rewritten using set based logic

hiteshj78
Starting Member

6 Posts

Posted - 2008-11-11 : 07:24:09

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

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2008-11-11 : 07:42:45

NO COMMIT OR ROLLBACK STATEMENT FOUND IN YOUR CODE AFTER "BEGIN TRANS"

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

hiteshj78
Starting Member

6 Posts

Posted - 2008-11-11 : 08:08:40

Thank you so much sigle cursor error is solved.

if i am execute in query it works fine..
When i execute in sp it gives error...same way..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources