EXEC PROCEDURE ERROR - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

EXEC PROCEDURE ERROR

Author

Topic

Harvey85
Starting Member

4 Posts

Posted - 2011-09-15 : 15:47:54

HI,
I have one stored procedure abc with 2 input and 2 output parameters , in which there is a cursor processing about 500 records. So on the basis of those output paramters as (0,1,2),i have to update another table(combinedriver3) with status field(success,warning,failure) and msgs.

I have one scenario:
Let say we exec proc abc and it process about 250 records and due to some reason or failure ,it stops. So now if we exec the proc abc again it should start from record 251 onwards.

Here when i try to use this scenario. I am able to process all 500 records but the this error msg

It gives me this error
Msg 266, Level 16, State 2, Procedure abc_sp, Line 0
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Kindly help me with this
will really appreciate
-------------------------------

here is the code:

create procedure abc_sp
as
begin

DECLARE
@sysID bigint,
@qnxtStatus char(15),
@vipAction char(15),
@TableName varchar(25),
@vipBatchID varchar(25),
@ReturnCode int,
--@return int,
--@ErrorMsg nvarchar(4000),
@ErrMsg nvarchar(4000)

----------------------------------------
OPEN InnerCursor
FETCH NEXT
FROM InnerCursor INTO @sysID,@vipAction,@TableName,@vipBatchID,@qnxtStatus
print 'Inner loop'
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @SYSID
IF @vipAction='Update' and @TableName='Specialty'----(3)
BEGIN
EXEC abc_specialty1_sp @sysID,@qnxtStatus,@ReturnCode output,@ErrMsg output
--
If @ReturnCode='0'-------------code for success
begin
update CombinedDriver3__tbl
set qnxtStatus='Success'
where sysID=@sysID and TABLEName=@TableName
and vipAction=@vipAction and qnxtStatus=@qnxtStatus
end
If @ReturnCode='1'-------------code for success
begin
update CombinedDriver3_tbl
set qnxtStatus='Warning'
where sysID=@sysID and TABLEName=@TableName
and vipAction=@vipAction and qnxtStatus=@qnxtStatus
end
if @ReturnCode='2'---------code for failure
begin
update CombinedDriver3_tbl
set qnxtStatus='Failure',qnxtStatusMessage=ISNULL (qnxtStatusMessage,'')+'<'+@ErrMsg+'>'
where sysID=@sysID and TABLEName=@TableName
and vipAction=@vipAction and qnxtStatus=@qnxtStatus
end

---
UPDATE CombinedDriver3_tbl
set
qnxtIUDateTimeStamp=GETDATE()
where sysID=@sysID and TABLEName=@TableName
and vipAction=@vipAction
END

FETCH NEXT
FROM InnerCursor INTO @sysID,@vipAction,@TableName,@vipBatchID,@qnxtStatus

END
---------------------------------------
print'endinnerloop'
CLOSE InnerCursor
DEALLOCATE InnerCursor
---------------------------------------
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-15 : 16:36:31

Your code doesn't have a transaction in it. So have you posted your actual code? Or is there a wrapper stored procedure for this that includes the transaction? Or maybe there's a transaction inside the stored procedure you are calling inside this one?

You've got a bug somewhere in your code. Your transaction isn't coded properly. So we need to figure out where your transaction is.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Harvey85
Starting Member

4 Posts

Posted - 2011-09-15 : 16:44:18

Here is the code inside the proc:

ALTER PROCEDURE [dbo].[abc_specialty1_sp_]
@sysID bigint,
@qnxtStatus char(15),
@ReturnCode AS INT OUTPUT,
@ErrMsg nvarchar(4000)OUTPUT

AS
BEGIN TRY

BEGIN TRAN T1
SET NOCOUNT ON;
UPDATE abc_specialty_tbl
SET
code=CASE
WHEN NPVS.code IS NOT NULL
THEN NPVS.specialt
ELSE NPS.specialty
END,
type=CASE
WHEN NPVS.type IS NOT NULL
THEN NPVS.type
ELSE NPS.type
END,
effdate=CASE
WHEN NPVS.effdate IS NOT NULL
THEN NPVS.effdate
ELSE NPS.effdate
END

FROM abc_specialty_tbl(NOLOCK) AS NPVS
INNER JOIN CombinedDriver3_tbl(NOLOCK) AS DVR
ON DVR.sysID=NPVS.sysID
where DVR.sysID=@sysID
and DVR.qnxtStatus=@qnxtStatus

-----------------------------

if @@ERROR=0
COMMIT TRAN T1
begin
if @qnxtStatus='Success'
begin
SET @ReturnCode = 0 --Code for success
end
if @qnxtStatus='Warning'
begin
SET @ReturnCode = 1 --Code for success
end
end
END TRY

BEGIN CATCH
IF @@TRANCOUNT>0 and @@ERROR<>0
ROLLBACK TRAN T1
SET @ReturnCode = 2 --Code for failure

SET @ErrMsg = ERROR_MESSAGE()

END CATCH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-15 : 17:00:50

Is this a typo in your code: THEN NPVS.specialt

Notice you are missing a y.

I think you are encountering a non-recoverable error, and I believe it's because of the typo.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources