Hello,
I’m trying to create a re-useable stored proc that will build an error
message that will be returned to the calling proc, I’ll call this proc
BuildErrorMsg_sp. Then I can keep all the error trapping code in all
my transact sps small, I don’t have to duplicate all the error
trapping logic in each stored proc. I just pass the ERROR_ functions
to this proc and it will build me a message to return to the client.

However, I seem to be having issues with the built-in system error
functions:
ERROR_SEVERITY(),ERROR_NUMBER(),ERROR_MESSAGE(),ER ROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE().

I tried to pass them both with the () and without. Attached is my
code.

Any would be appreciated. Or if anyone already has a proc like
this or a better way to have reusable error trapping, don’t hesitate
to pass it along. I’m open to ideas.

Thanks.

-- Code below --
use Junk
go

if exists (select * from sysobjects where id =
object_id('dbo.TestErrorSP_sp') and sysstat & 0xf = 4)
drop procedure dbo.TestErrorSP_sp
GO
Create Procedure TestErrorSP_sp
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
-- Try to do something that will generate an error
DECLARE @iValue int
Select @iValue = 1/0
END TRY

--Error Trapping
BEGIN CATCH
DECLARE @RetMsg nvarchar(4000)
--Exec BuildErrorMsg_sp
ERROR_SEVERITY(),ERROR_NUMBER(),ERROR_MESSAGE(),ER ROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE(),
@RetMsg OUTPUT
Exec BuildErrorMsg_sp
ERROR_SEVERITY,ERROR_NUMBER,ERROR_MESSAGE,ERROR_ST ATE,ERROR_LINE,ERROR_PROCEDURE,
@RetMsg OUTPUT
RAISERROR(@RetMsg, ERROR_SEVERITY(), ERROR_NUMBER())

--PRINT '@RetMsg: ' + @RetMsg

END CATCH

END
Return(0)
GO
GRANT EXECUTE ON dbo.TestErrorSP_sp TO WebUser
GO

-- Test it
-- Exec TestErrorSP_sp

/
*================================================= ===========================================*/

if exists (select * from sysobjects where id =
object_id('dbo.BuildErrorMsg_sp') and sysstat & 0xf = 4)
drop procedure dbo.BuildErrorMsg_sp
GO
Create Procedure BuildErrorMsg_sp
@ErrorSeverity int = -1,
@ErrorNumber int = -1,
@ErrorMessage nvarchar(4000) = '',
@ErrorState int = -1,
@ErrorLine int = -1,
@ErrorProc nvarchar(200) = '',
@RetMsg nvarchar(4000) OUTPUT
AS
BEGIN
SET NOCOUNT ON

------------------------------------------------------------------------------------------------
-- validate parameters
------------------------------------------------------------------------------------------------
If (
IsNull(@ErrorSeverity, -1) = -1 OR
IsNull(@ErrorNumber, -1) = -1 OR
IsNull(@ErrorMessage, '') = '' OR
IsNull(@ErrorState, -1) = -1 OR
IsNull(@ErrorLine, -1) = -1 OR
IsNull(@ErrorProc, '') = ''
)
BEGIN
RAISERROR 30000 'BuildErrorMsg_sp: validate parameters failure'
Return
END

BEGIN
--Declare @ErrMsg nvarchar(1000) /**/ SET @ErrMsg = ''
SET @RetMsg = 'Error ' + CAST(@ErrorNumber AS VARCHAR(10)) +
' in procedure: ' + @ErrorProc +
' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) +
' Error text: ' + @ErrorMessage

--PRINT @RetMsg
Return

END
END
Return(0)
GO
GRANT EXECUTE ON dbo.BuildErrorMsg_sp TO WebUser
GO

/*
-- test BuildErrorMsg_sp directly

DECLARE @RetMsg nvarchar(4000)
Exec BuildErrorMsg_sp -100, 256,'Some Error Message', 99, 46,
'MyProc_sp', @RetMsg OUTPUT
Select '@RetMsg: ' + @RetMsg

*/

You cannot specify a function call in a procedure call, like:

EXEC myproc @parm = TheFunction()

You have to assign the value from the function to a variable and pass that variable instead.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"GiJeet" <gijeet@yahoo.com> wrote in message
news:cdd6a5af-1128-4737-8f53-da97bf5778cb@b64g2000hsa.googlegroups.com...
Hello,
I’m trying to create a re-useable stored proc that will build an error
message that will be returned to the calling proc, I’ll call this proc
BuildErrorMsg_sp. Then I can keep all the error trapping code in all
my transact sps small, I don’t have to duplicate all the error
trapping logic in each stored proc. I just pass the ERROR_ functions
to this proc and it will build me a message to return to the client.

However, I seem to be having issues with the built-in system error
functions:
ERROR_SEVERITY(),ERROR_NUMBER(),ERROR_MESSAGE(),ER ROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE().

I tried to pass them both with the () and without. Attached is my
code.

Any would be appreciated. Or if anyone already has a proc like
this or a better way to have reusable error trapping, don’t hesitate
to pass it along. I’m open to ideas.

Thanks.

-- Code below --
use Junk
go

if exists (select * from sysobjects where id =
object_id('dbo.TestErrorSP_sp') and sysstat & 0xf = 4)
drop procedure dbo.TestErrorSP_sp
GO
Create Procedure TestErrorSP_sp
AS
BEGIN
SET NOCOUNT ON

BEGIN TRY
-- Try to do something that will generate an error
DECLARE @iValue int
Select @iValue = 1/0
END TRY

--Error Trapping
BEGIN CATCH
DECLARE @RetMsg nvarchar(4000)
--Exec BuildErrorMsg_sp
ERROR_SEVERITY(),ERROR_NUMBER(),ERROR_MESSAGE(),ER ROR_STATE(),ERROR_LINE(),ERROR_PROCEDURE(),
@RetMsg OUTPUT
Exec BuildErrorMsg_sp
ERROR_SEVERITY,ERROR_NUMBER,ERROR_MESSAGE,ERROR_ST ATE,ERROR_LINE,ERROR_PROCEDURE,
@RetMsg OUTPUT
RAISERROR(@RetMsg, ERROR_SEVERITY(), ERROR_NUMBER())

--PRINT '@RetMsg: ' + @RetMsg

END CATCH

END
Return(0)
GO
GRANT EXECUTE ON dbo.TestErrorSP_sp TO WebUser
GO

-- Test it
-- Exec TestErrorSP_sp

/
*================================================= ===========================================*/

if exists (select * from sysobjects where id =
object_id('dbo.BuildErrorMsg_sp') and sysstat & 0xf = 4)
drop procedure dbo.BuildErrorMsg_sp
GO
Create Procedure BuildErrorMsg_sp
@ErrorSeverity int = -1,
@ErrorNumber int = -1,
@ErrorMessage nvarchar(4000) = '',
@ErrorState int = -1,
@ErrorLine int = -1,
@ErrorProc nvarchar(200) = '',
@RetMsg nvarchar(4000) OUTPUT
AS
BEGIN
SET NOCOUNT ON

------------------------------------------------------------------------------------------------
-- validate parameters
------------------------------------------------------------------------------------------------
If (
IsNull(@ErrorSeverity, -1) = -1 OR
IsNull(@ErrorNumber, -1) = -1 OR
IsNull(@ErrorMessage, '') = '' OR
IsNull(@ErrorState, -1) = -1 OR
IsNull(@ErrorLine, -1) = -1 OR
IsNull(@ErrorProc, '') = ''
)
BEGIN
RAISERROR 30000 'BuildErrorMsg_sp: validate parameters failure'
Return
END

BEGIN
--Declare @ErrMsg nvarchar(1000) /**/ SET @ErrMsg = ''
SET @RetMsg = 'Error ' + CAST(@ErrorNumber AS VARCHAR(10)) +
' in procedure: ' + @ErrorProc +
' Line: ' + CAST(@ErrorLine AS VARCHAR(10)) +
' Error text: ' + @ErrorMessage

--PRINT @RetMsg
Return

END
END
Return(0)
GO
GRANT EXECUTE ON dbo.BuildErrorMsg_sp TO WebUser
GO

/*
-- test BuildErrorMsg_sp directly

DECLARE @RetMsg nvarchar(4000)
Exec BuildErrorMsg_sp -100, 256,'Some Error Message', 99, 46,
'MyProc_sp', @RetMsg OUTPUT
Select '@RetMsg: ' + @RetMsg

*/