September 15, 2011 at 1:51 pm
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
September 15, 2011 at 1:56 pm
This seems especially programmatic for SQL. Is there a reason you are doing this all in an sp instead of programmatically? I'll take a look at this in the meantime...
Jared
Jared
CE - Microsoft
September 15, 2011 at 2:02 pm
Well, I have to call the 15 different stored proc through cursor based on conditions. This is onlt 1 of them , but all have same logic
September 15, 2011 at 2:03 pm
What is the code from abc_specialty1_sp? You may need to use a SAVEPOINT in your "nested" procedure. This way you can rollback to that without resetting the Transaction Count.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37247#112267
Thanks,
Jared
Jared
CE - Microsoft
September 15, 2011 at 2:12 pm
this the stored proc which executed by cursor
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
ROLLBACK 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
September 15, 2011 at 2:19 pm
I'm no expert in this... but I don't see any commit on your transaction. Only rollbacks.
Thanks,
Jared
Jared
CE - Microsoft
September 15, 2011 at 2:31 pm
I don't see any reason in here at all that you need a cursor. You aren't doing anything but updates. This could be converted to be entirely set based and the performance would be milliseconds instead of seconds (or maybe minutes, hours). The cursor approach might work ok for now because there is not too much data, but down the road this is going to be a major performance hurdle.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2011 at 2:35 pm
Sean Lange (9/15/2011)
I don't see any reason in here at all that you need a cursor. You aren't doing anything but updates. This could be converted to be entirely set based and the performance would be milliseconds instead of seconds (or maybe minutes, hours). The cursor approach might work ok for now because there is not too much data, but down the road this is going to be a major performance hurdle.
I second this. Almost all cursors can be modified to be set based. I also believe that if it REALLY needs a cursor, it is probably best done by a program and not script.
Thanks,
Jared
Jared
CE - Microsoft
September 15, 2011 at 2:35 pm
ohh my mistake , here is the query
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
September 15, 2011 at 2:39 pm
That looks like your transaction count will be consistent at this point. If you want to try improve this process and get rid of those **cough**cursors**cough** let us know and we can have a go at it. If you want to try that have a look at the link in my signature about best practices for posting ddl, sample data (insert statements) and desired output based on the sample data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2011 at 2:40 pm
SEAN,
I have to use the cursor ,as according to the requirement, so no wayout.
September 15, 2011 at 2:45 pm
Something "seems" not right here... Why is your BEGIN TRAN inside of the TRY? I am not saying it is wrong, but I have never seen it done this way and it does not make sense to me.
Should be more like this:
begin tran
begin try
-- some code
end try
begin catch
select
ErrorNumber=error_number(),
ErrorSeverity=error_severity(),
ErrorState=error_state(),
ErrorProcedure=error_procedure(),
ErrorLine=error_line(),
ErrorMessage=error_message()
set @rc=1
set @str='usp_sql_errorlog_scan: error occured while running <<< insert #srv values ... >>> script'
print @str
raiserror (@str,1,2) with log
if @@trancount>0
rollback
end catch
if @@trancount>0
commit
Also, see these articles to eventually get rid of your cursor:
http://www.sqlservercentral.com/articles/cursors/65040/
http://www.sqlservercentral.com/articles/SQL+Puzzles/eliminatingcursors/2223/
Jared
CE - Microsoft
September 15, 2011 at 2:48 pm
hardial_bhatia (9/15/2011)
SEAN,I have to use the cursor ,as according to the requirement, so no wayout.
Your boss actually mandates that you use a cursor?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2011 at 2:49 pm
Is this homework? If not, the "requirement" should be suggested to be changed for efficiency 🙂
Jared
Jared
CE - Microsoft
September 15, 2011 at 2:54 pm
jared-709193 (9/15/2011)
Is this homework? If not, the "requirement" should be suggested to be changed for efficiency 🙂Jared
And if it is homework you should suggest it be changed to your professor, and maybe a mention that they should be embarrassed about teaching bad code practice to students.
Either way you slice it, a cursor is the wrong way to tackle this. To steal a phrase from Jeff Moden, "Don't think about what you want to do to a row, instead think about what you want to do to a column".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply