November 26, 2006 at 2:53 am
I'm having problem with output parameters and TRY..CATCH blocks.
When I call a stored procedure in a TRY..CATCH block and there are no errors then everything works fine.
When there is an error in the procedure the output parameter is not returned.
See my example, there are 2 inserts in the stored procedure, the second insert will cause an error.
I would expect the output parameter to have the value 'insert value null'.
This is not true, the value is not changed.
Is is possible to use output parameters in a TRY..CATCH block?
Regards, Peter.
create
table table1 (column1 varchar(10) not null)
go
CREATE
PROCEDURE spInsTable1
@out varchar(100) OUTPUT
AS
set @out = 'insert value yes'
insert
table1 values ('yes')
set
@out = 'insert value null'
insert
table1 values (null)
return
go
------------------------------------------------------
declare
@out varchar(100)
,@error_message nvarchar(4000)
,@error_nr int
set @out = 'start test'
BEGIN
TRY
exec spInsTable1 @out output
END TRY
BEGIN CATCH
SELECT
@error_nr
= ERROR_NUMBER(),
@error_message
= ERROR_MESSAGE();
END
CATCH;
select
@out, @error_nr, @error_message
November 27, 2006 at 5:32 am
Hi Peter
It is possible to use the out parameter in a try catch block. Move the try catch into your stored proc. This will then everytime return the out parameter as you require. Set the value of the output parameter to whatever message you desire in the catch ( normally return the Error_Message()).
Then you can select the output paramater as in your example
,l0n3i200n
November 27, 2006 at 6:03 am
Hi there, thanks for your response!
I have a number of procedures like this, and they are doing a lot of inserts/updates/deletes. So I really want just one try..catch block. In this block I want to call the procedures. if anything goes wrong then I'd like to know where it went wrong. That is why I need the output parameter.
Peter.
November 27, 2006 at 6:50 am
Peter,
The I think the reason your not getting the parameter back is because your stored procedure breaks at the line where the error is. I.e. the second insert.
As it never hits the return statement the value of @out does not get passed back into the calling procedure.
Play with a second Try Catch block and see what happens.
November 27, 2006 at 6:54 am
I used sql 2000. While I debuged, I used print out the sql statement, so I know what is wrong with that.
CREATE PROCEDURE dbo.CCPercent
@quiz VARCHAR( 25 )=null,
@cc nvarchar(125) = NULL,
@debug bit = 0
as
DECLARE @sql nvarchar(4000)
begin
set @sql='SELECT E.JobCCNo AS CostCenter,D.QuizName, E.Department
,COUNT(DISTINCT E.EmplNo) AS TCount
,COUNT(DISTINCT T.EmplNo) As Completed
,COUNT(DISTINCT E.EmplNo) - COUNT(DISTINCT T.EmplNo) AS InCompleted
FROM OcchEmp E
CROSS JOIN (
SELECT DISTINCT quizname
FROM tblCurrentWinTrainingLog T1 ) D
left outer JOIN tblCurrentWinTrainingLog T ON E.EmplNo = T.EmplNo AND D.quizname = T.quizname where 1=1 '
IF @cc is not null
set @sql= @sql + ' AND E.JObCCNO in('+ @cc +')'
if @quiz is not null
set @sql= @sql + ' And D.QuizName = '''+ @quiz +''''
set @sql= @sql+' GROUP BY E.JobCCNo, D.quizname, E.Department ORDER BY E.JobCCNo, D.quizname '
set @sql= @sql
if @quiz ='-1' and @quiz=''
set @sql= @sql
IF @Debug = 1
begin
PRINT @sql PRINT ''
END
exec (@sql)
end
November 27, 2006 at 7:10 am
as suggested by l0n3i200n
have your stored proc like so
ALTER
PROCEDURE [dbo].[spInsTable1]
@out varchar(100) OUTPUT
AS
BEGIN TRY
set @out = 'insert value yes'
BEGIN TRY
insert table1 values ('yes')
END TRY
BEGIN CATCH
END CATCH
END
TRY
BEGIN
CATCH
-- SELECT ERROR_NUMBER(),
-- ERROR_MESSAGE();
END
CATCH;
BEGIN
TRY
set @out = 'insert value null'
BEGIN TRY
insert table1 values (NULL)
END TRY
BEGIN CATCH
END CATCH
END
TRY
BEGIN
CATCH
-- SELECT ERROR_NUMBER(),
-- ERROR_MESSAGE();
END
CATCH;
RETURN
November 27, 2006 at 10:31 am
To do this really well, you are going to have to nest TRY/CATCH blocks in each called proc, I think...
The nested CATCH blocks in each called proc can call RAISERROR to promote the error to your outer CATCH. This way your outer CATCH can handle it consistently, however you choose to do that, whether it involves an output param or anything else.
The docs (Using TRY...CATCH in Transact-SQL) have a good example of how to call RAISERROR within the nested CATCH block. There is a specific section of the topic on this (TRY…CATCH with RAISERROR). I would add a couple of points:
* -- watch your severity levels and determine some strategy, use the same broad classifications that the product uses
* -- it's a good idea to create user-defined error numbers rather than just passing the one you got, as shown in the docs. One way to do this is to use ERROR_NUMBER() + 50000 or something similar, although you can always preserve the original ERROR_NUMBER() in the message you raise. Another way is to designate *one* user defined error number indicating "errors promoted to this particular calling proc" and register this with sp_addmessage, again you can still find a way to have the message incorporate the original error information.
* -- I would add some sort of check (whether an extra BIT param with a default, or whatever you like) inside the nested CATCH block, so that you can react somewhat differently if the proc was *not* called by another one -- this is for unit testing purposes.
* -- this is prolly a good situation for a personalized procedural template, which you can share/document/discuss with everyone who is writing the sub-procs. If memory serves there was a good article about doing this not too long ago on this site, maybe somebody can chime in and reference...?
>L<
November 28, 2006 at 3:35 am
Hmmm, I hoped I could use only one TRY/CATCH...
thanks!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply