November 27, 2006 at 3:53 am
Since I did not get any reactions in the development group, I will try to ask my question here:
I'm having a 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
drop PROCEDURE spInsTable1
go
CREATE PROCEDURE spInsTable1
@out varchar(100) OUTPUT
AS
select @out = 'insert value yes'
insert table1 values ('yes')
select @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 = @out output
END TRY
BEGIN CATCH
SELECT
@error_nr = ERROR_NUMBER(),
@error_message = ERROR_MESSAGE();
END CATCH;
select @out, @error_nr, @error_message
November 28, 2006 at 4:22 am
try something like this:
create table table1 (column1 varchar(10) not null)
go
drop PROCEDURE spInsTable1
go
CREATE PROCEDURE spInsTable1
@out varchar(100) OUTPUT
AS
begin try
select @out = 'insert value yes'
insert table1 values ('yes')
select @out = 'insert value null'
insert table1 values (null)
end try
begin catch
print @out
end catch;
return
go
------------------------------------------------------
declare @out varchar(100)
,@error_message nvarchar(4000)
,@error_nr int
set @out = 'start test'
BEGIN TRY
exec spInsTable1 @out = @out output
END TRY
BEGIN CATCH
SELECT
@error_nr = ERROR_NUMBER(),
@error_message = ERROR_MESSAGE();
END CATCH;
select @out, @error_nr, @error_message
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply