ZANTAR
asked on
Suppressing SQL Server error messages so they do not appear when executing my stored procedure.
I have the following stored procedure to get the table count. If the table does not exist, the stored procedure will return 3, but it also is giving me an error message which I DON'T WANT DISPLAYED as it is not an error.
/* Start stored procedure */
if exists (select * from sysobjects where id = object_id(N'[DBO].[TEST_TA
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [DBO].[TEST_TABLE_COUNT]
GO
CREATE PROCEDURE TEST_TABLE_COUNT
@parSchema VARCHAR(30),
@parTableName VARCHAR(30),
@parRowCount NUMERIC OUTPUT
AS
SET NOCOUNT ON
DECLARE
@parError INT,
@v_strSQL NVARCHAR(1000)
BEGIN
SET @parRowCount = 0
SET @v_strSQL = 'SELECT @parRowCount = COUNT(1) FROM ' + @parSchema + '.' + @parTableName
BEGIN
EXEC @parError = sp_executesql @v_strSQL, N'@parRowCount INT OUTPUT', @parRowCount OUTPUT
SET @parError = @@ERROR
IF @parError <> 0
BEGIN
GOTO ERR_HANDLER
END
RETURN 0 -- No errors so return success
ERR_HANDLER:
BEGIN
IF @parError = 208
BEGIN
RETURN 3 -- Success - table does not exist
END
ELSE
BEGIN
Print 'Error Occured with number: ' + Cast(@parError as varchar)
RETURN 1 -- Failed - Another error occured
END
END
END
RETURN 2 -- Failed, should never get to this point
END
/* End stored procedure */
Here is the code I'm using to execute the stored procedure:
/* Start of execute code */
DECLARE
@V_INT_RESULT INT,
@V_INT_COUNT INT
BEGIN
EXECUTE @V_INT_RESULT = TEST_TABLE_COUNT 'DBO','TABLE_DOES_NOT_EXIS
PRINT 'RESULT: ' + CAST(@V_INT_RESULT AS VARCHAR)
PRINT 'COUNT: ' + CAST(@V_INT_COUNT AS VARCHAR)
END
/* End of execute code */
Here is the result it is displaying to me:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.TABLE_DOES_NOT_EXISTS
RESULT: 3
COUNT: 0
I only want it to display:
RESULT: 3
COUNT: 0
How do I modify my stored procedure to suppress the error message so that it doesn't get displayed to the user?
/* Start stored procedure */
if exists (select * from sysobjects where id = object_id(N'[DBO].[TEST_TA
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [DBO].[TEST_TABLE_COUNT]
GO
CREATE PROCEDURE TEST_TABLE_COUNT
@parSchema VARCHAR(30),
@parTableName VARCHAR(30),
@parRowCount NUMERIC OUTPUT
AS
SET NOCOUNT ON
DECLARE
@parError INT,
@v_strSQL NVARCHAR(1000)
BEGIN
SET @parRowCount = 0
SET @v_strSQL = 'SELECT @parRowCount = COUNT(1) FROM ' + @parSchema + '.' + @parTableName
BEGIN
EXEC @parError = sp_executesql @v_strSQL, N'@parRowCount INT OUTPUT', @parRowCount OUTPUT
SET @parError = @@ERROR
IF @parError <> 0
BEGIN
GOTO ERR_HANDLER
END
RETURN 0 -- No errors so return success
ERR_HANDLER:
BEGIN
IF @parError = 208
BEGIN
RETURN 3 -- Success - table does not exist
END
ELSE
BEGIN
Print 'Error Occured with number: ' + Cast(@parError as varchar)
RETURN 1 -- Failed - Another error occured
END
END
END
RETURN 2 -- Failed, should never get to this point
END
/* End stored procedure */
Here is the code I'm using to execute the stored procedure:
/* Start of execute code */
DECLARE
@V_INT_RESULT INT,
@V_INT_COUNT INT
BEGIN
EXECUTE @V_INT_RESULT = TEST_TABLE_COUNT 'DBO','TABLE_DOES_NOT_EXIS
PRINT 'RESULT: ' + CAST(@V_INT_RESULT AS VARCHAR)
PRINT 'COUNT: ' + CAST(@V_INT_COUNT AS VARCHAR)
END
/* End of execute code */
Here is the result it is displaying to me:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DBO.TABLE_DOES_NOT_EXISTS
RESULT: 3
COUNT: 0
I only want it to display:
RESULT: 3
COUNT: 0
How do I modify my stored procedure to suppress the error message so that it doesn't get displayed to the user?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Languages like VB allow you to do On Error GoTo ..... which does mean you can effectively ignore errors. However, SQL does not support this and so you cannot ignore errors in this way like you want to.
The approach that you should take is to perform validation where you can, before a statement that may generate an error (like bhess1 has demonstrated). You can wrap common validation checks into generic functions (e.g. a function to check if a certain table exists, taking the table name as a parameter).
You should also capture any errors in your front end. Can you not do this? Your front end could then decide whether or not to show them to the user.
Here is a quote I found :
It's not possible to suppress error messages from within T-SQL. Error messages are always returned to the client. If you don't want your users to see these raw error messages, you should handle them in your front-end applications. For example, if you are using ADO from ASP to connect to SQL Server, you would do something like the following:
On Error Resume Next
Set Rs = Conn.Execute ("INSERT INTO MyTable (1,'How to migrate from Oracle to SQL Server','Book'")
If Err.Number <> 0 Then Response.Write ("Error occurred while inserting new data")
On Error GoTo 0
Cheers
The approach that you should take is to perform validation where you can, before a statement that may generate an error (like bhess1 has demonstrated). You can wrap common validation checks into generic functions (e.g. a function to check if a certain table exists, taking the table name as a parameter).
You should also capture any errors in your front end. Can you not do this? Your front end could then decide whether or not to show them to the user.
Here is a quote I found :
It's not possible to suppress error messages from within T-SQL. Error messages are always returned to the client. If you don't want your users to see these raw error messages, you should handle them in your front-end applications. For example, if you are using ADO from ASP to connect to SQL Server, you would do something like the following:
On Error Resume Next
Set Rs = Conn.Execute ("INSERT INTO MyTable (1,'How to migrate from Oracle to SQL Server','Book'")
If Err.Number <> 0 Then Response.Write ("Error occurred while inserting new data")
On Error GoTo 0
Cheers
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
I have to agree with adathelad, I don't think there is a way to supress the error message...
CHeers
CHeers
1. You can create a new language in master.dbo.syslanguages
2. Add new messages to master.dbo.sysmessages with the new language and lower severity.
3. Change a language to the new one
4. Run SP
5. Change a language to normal (English,...)
I didn't test it, BACKUP MASTER.MDF+LDF FIRST and try it.
2. Add new messages to master.dbo.sysmessages with the new language and lower severity.
3. Change a language to the new one
4. Run SP
5. Change a language to normal (English,...)
I didn't test it, BACKUP MASTER.MDF+LDF FIRST and try it.
ASKER
This is the final solution I will have to implement, even though I had hoped I could suppress the errors.
I contacted Microsoft support and they said it was not possible with the way the database processes the sql statements.
Thanks to everyone who responded.
I contacted Microsoft support and they said it was not possible with the way the database processes the sql statements.
Thanks to everyone who responded.
ASKER
The above stored procedure is just a simple sample of one case, so searching for the table to exist will not work in all cases.
The solution I'm looking for is similar to the way "SET NOCOUNT ON" works. If this parameter is set to off, the stored procedure would return the number of records to the screen each time this stored procedure is executed without needing to return the value. When it is on, the value has to be explicitly returned and will not show up the screen otherwise.
This is what I'm trying to achieve. If an error occurs, it will be printed to the screen only by the code: Print 'Error Occured with number: ' + Cast(@parError as varchar)
In the case of the table not existing, the code would not return an error, but the stored procedure would return the value 3 only.
In the real world, I have the error write to a database table for future use, but I don't want it displayed to the user who is executing the stored procedure.