October 6, 2010 at 9:52 am
Hi,
I have this structure of stored procedure :
CREATE PROCEDURE sp1
(
@ret varchar(100) output
)
AS
BEGIN
BEGIN TRY
SELECT @ret = 'Hello world'
raiserror (@$errmsg, 16, 1);
END TRY
--========
BEGIN CATCH
....
END CATCH
END
Now, when the stored procedure returns, the @ret variable has no value in it.... Why is that ???
Any help will b appreciated...
October 6, 2010 at 11:53 am
You don't seem to returning the @ret from the stored procedure...with or without the raiserror your stored procedure will not return anything...
maybe something like this?
CREATE PROCEDURE sp1(@ret varchar(100) output)
AS
BEGIN
BEGIN TRY
SELECT @ret = 'Hello world'
-- this will return the value of @ret
-- after this execution will jump to the catch block
SELECT @ret
raiserror (@$errmsg, 16, 1);
END TRY
========
BEGIN CATCH
....
END CATCH
END
October 7, 2010 at 4:17 am
there is nothing wrong with your SP code.
just check the exec statement.
declare @ret varchar(100)
exec sp1 @ret output
select @ret
This will definitely work.
raiserror makes no difference to the values assigned to the variables.
October 7, 2010 at 5:10 am
Good catch - re-looked at the original post again and realized that it was an output parameter the OP was talking about...
October 7, 2010 at 5:28 am
Im trying to do so, checked and double checked everything...
October 7, 2010 at 5:46 am
Can you post some sample code of where it isn't working for you? For e.g. how are you trying to access the output parameter, how are you calling this procedure, what's in the catch block of the procedure etc...the code below works fine:
--DROP PROCEDURE dbo.TestSP;
GO
CREATE PROCEDURE dbo.TestSP(@ret varchar(100) output)
AS
BEGIN
BEGIN TRY
SELECT @ret = 'Hello'
RAISERROR ('ERROR', 16, 1);
END TRY
BEGIN CATCH
-- not batch terminator
RAISERROR('error',16,1);
-- batch terminator
--RAISERROR('error',17,1);
END CATCH
END
GO
DECLARE @ret varchar(100)
-- errors out but @ret available as not batch terminating error
EXEC dbo.TestSP @ret OUTPUT
SELECT @ret
SELECT @ret+': Test'
GO
October 7, 2010 at 7:00 am
The T-SQL code i posted in the original post was an attempt to simplify the problem, the actual code looks more like this :
CREATE PROCEDURE dbo.IntSP(@ret varchar(100) output)
AS
BEGIN
BEGIN TRY
SELECT @ret = 'Hello'
RAISERROR ('ERROR', 16, 1);
END TRY
BEGIN CATCH
-- not batch terminator
RAISERROR('error',16,1);
-- batch terminator
--RAISERROR('error',17,1);
END CATCH
END
CREATE PROCEDURE dbo.ExtSP(@ret varchar(100) output)
AS
BEGIN
BEGIN TRY
EXEC dbo.IntSP @ret output
END TRY
BEGIN CATCH
-- not batch terminator
RAISERROR('error',16,1);
-- batch terminator
--RAISERROR('error',17,1);
END CATCH
END
After IntSP reaches to the "RAISERROR('error',16,1);" in the "CATCH" block, the instruction pointer returns to the "CATCH" block of ExtSP and @ret is empty...
NO IDEA what to do...
October 7, 2010 at 7:28 am
You've got nested SPs. With the TRY...CATCH block and nested SPs one way to get things to work in your situation is to have the called SP return a status code indicating success or failure and then handle that in the calling SP. This will not cause the calling SP execution to jump to the CATCH block and you can still access the output variable.
This way of doing things depends on what you're doing within your code though.
--DROP PROCEDURE dbo.IntSP;
--DROP PROCEDURE dbo.ExtSP;
GO
CREATE PROCEDURE dbo.IntSP(@ret varchar(100) output)
AS
BEGIN
BEGIN TRY
SELECT @ret = 'Hello'
RAISERROR ('ERROR', 16, 1);
RETURN 0 -- no error occurred here
END TRY
BEGIN CATCH
-- inner SP - return code signifying error or success
RETURN -1
END CATCH
END
GO
CREATE PROCEDURE dbo.ExtSP(@ret varchar(100) output)
AS
BEGIN
DECLARE @RetCode int,@ErrMsg nvarchar(2048);
BEGIN TRY
EXEC @RetCode = dbo.IntSP @ret output
IF @RetCode = -1
BEGIN
SELECT @ret OutputParam,'ErrorOccurredInIntSP' ErrorStatus
END
ELSE
BEGIN
SELECT @ret OutputParam,'ErrorOccurredInIntSP'
END
END TRY
BEGIN CATCH
SET @ErrMsg = ERROR_MESSAGE();
RAISERROR(@ErrMsg,16,1);
END CATCH
END
GO
DECLARE @ret varchar(100)
EXEC dbo.ExtSP @ret OUTPUT
SELECT @ret+': TEST'
October 7, 2010 at 7:43 am
This solution may work, but :
a) I have another constraints that i have to satisfy (i have nested transactions as well, so i really need the rethrow exception mechanism...)
b) I'm new to T-SQL development and im curious about this phenomenon... I'm wondering WHY this happens...
October 7, 2010 at 9:49 am
Besides, when the SP invokes "raiserror", the error code given as a parameter to "raiseerror" is returned from the SP.
If no error occurs, then it returns 0.
So, i don't really need to explicitly return the value. (right ?)
October 7, 2010 at 11:12 am
Yep - if you RAISERROR then you don't need to explicitly return a value.
However if you return the error number explicitly in the inner SP from the catch block (using a RETURN) without raising an error you can have access to the output parameter (as opposed to raising the error in the catch block of the inner SP and getting a NULL value for the output parameter).
About why the output variable is NULL in this situation (when you raiserror from the inner SP) - I don't really know and my guess is that since the execution jumps to the catch block in the outer SP the output variable is not being set from the inner SP call and it remains NULL.
You can use nested transactions if you use the explicit return (as in the sample code I posted) - I agree that is more intuitive to use the inbuilt raiserror to rethrow the exceptions rather than a returning values but it looks like that option will not work due to the output parameter not being available.
November 17, 2011 at 10:59 am
winash (10/7/2010)
Can you post some sample code of where it isn't working for you? For e.g. how are you trying to access the output parameter, how are you calling this procedure, what's in the catch block of the procedure etc...the code below works fine:
--DROP PROCEDURE dbo.TestSP;
GO
CREATE PROCEDURE dbo.TestSP(@ret varchar(100) output)
AS
BEGIN
BEGIN TRY
SELECT @ret = 'Hello'
RAISERROR ('ERROR', 16, 1);
END TRY
BEGIN CATCH
-- not batch terminator
RAISERROR('error',16,1);
-- batch terminator
--RAISERROR('error',17,1);
END CATCH
END
GO
DECLARE @ret varchar(100)
-- errors out but @ret available as not batch terminating error
EXEC dbo.TestSP @ret OUTPUT
SELECT @ret
SELECT @ret+': Test'
GO
Hmmm, the code above does not work pasted as is into SSMS,
it just returns the following screen, with the option of Return As Text (not grid):
"
(1 row(s) affected)
(1 row(s) affected)
Msg 50000, Level 18, State 2, Procedure TestSP, Line 10
error
"
Any ideas?
Thank you.
R
November 17, 2011 at 11:03 am
This thread is pretty old. If you are facing some issues, I would suggest you to start a new one.
November 17, 2011 at 11:34 am
OK, FYI, function of error level, works up to 16, 17+ - OUTPUT var is cleared.
R
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply