none
What is the best practice when returning custom messages from stored procedure

    Question

  • Hi All,

    I am little bit confused regarding the best way of returning custome messages from stored procedure.

    I am trying to do some thing like this

    1. I have table where I need to check and insert user details like meail, name etc(Email being the primary key)

    2. If the email already exists , send a custom message to user that ' user already exist'

    3. Else if not found , then insert new user and again send a custome message 'Registered Successfully'

    As far as I think, we can do either by RAISERROR or using an output parameter

    Currently I am using RIASERROR method of sql to send. If am not wrong RAISERROR terminates the stored procedure based on the Error Severity.

    Can anyone help me to understand which is the best practice used in industry as I am working on an ecoomerce application

    Your help is appreciated,

    Thanks,

    mds

    Wednesday, August 21, 2013 7:35 AM

Answers

  • The messages from a stored procedure can be;

    1. Business Rule exceptions (in your case duplicate email) that has to be raised as an error message. This will terminate the execution of the procedure or continue based on the business rule implemented

    2. System / Database messages that also need to be passed to the calling application as an error message to log or trouble shoot the issue. This will definitley terminate the execution of the procedure.

    3. Informational messages passed to the calling application, like 'Registered Successfully', that the execution can be continued.

    The first and second category can be treated as an error message and raised as an error. But the third one, the informational message, this cannot be raised as an error. This can be done by one of the following methods.

    a. An output parameter from the procedure that set a value, say an integer value of 0, that indicate success and, handle this in application

    b. A select query of proper message returned to the calling application as a result set like, SELECT 'Registered Successfully' AS Message

    c. The error severity from 1 to 10 is considered as informational message by most of the data access APIs. So issue a RAISERROR having a severity between 1 to 10 with proper message.

    I think the simplest is the first one and the most intutive is the third. Data access APIs and classes like SQLClient, OLEDbClient etc supports a special event handler for processing informational messages. So this will be more meanigful.

    For example here is a skeleton code as an example:

    CREATE TABLE TestUser
    (
    EMail VARCHAR(100),
    Name VARCHAR(10)
    )
    GO
    
    CREATE PROCEDURE PR_SaveUser
    (
    @EMail VARCHAR(100),
    @Name VARCHAR(100)
    )
    AS
    	BEGIN
    
    		SET NOCOUNT ON;
    
    		IF EXISTS(SELECT 1 FROM TestUser WHERE EMail = @EMail)
    			BEGIN
    
    				-- Custom Error Message
    				RAISERROR ('User Already Exists', 16, 1);
    				RETURN 1;
    
    			END;
    
    		ELSE
    			BEGIN
    				
    				BEGIN TRY
    				
    					BEGIN TRANSACTION;
    					INSERT INTO TestUser VALUES (@EMail, @Name);
    					COMMIT TRANSACTION;
    
    					-- Severity 1 - 10 is informational Message;
    					RAISERROR ('User Registered Successfully', 10, 1);
    					RETURN 0;
    
    				END TRY
    				BEGIN CATCH
    					
    					IF @@TRANCOUNT > 0 ROLLBACK;
    
    					DECLARE @ErrMsg AS NVARCHAR(4000) = '',
    							@ErrSeverity AS INT = 0,
    							@ErrState AS INT = 0;
    
    					-- Server/ Database errors
    					SELECT @ErrMsg = 'Error in PR_SaveUser : ' + ERROR_MESSAGE(), 
    							@ErrSeverity = ERROR_SEVERITY(), 
    							@ErrState = ERROR_STATE();
    
    					RAISERROR (@ErrMsg, @ErrSeverity, @ErrState);
    
    				END CATCH
    			END;
    
    	END;
    GO
    
    -- Will succeed with info message 'User Registered Successfully'
    EXEC PR_SaveUser 'alice@acme.com', 'Alice';
    EXEC PR_SaveUser 'bob@acme.com', 'Bob';
    
    -- Will throw the custom error 'User Already Exists' and terminate the procedure
    EXEC PR_SaveUser 'alice@acme.com', 'Alice';
    
    -- Will throw 'Error in PR_SaveUser : String or binary data would be truncated.'
    EXEC PR_SaveUser 'kk@acme.com', 'Krishnakumar'; -- Name is VARCHAR(10)
    
    DROP PROCEDURE PR_SaveUser
    GO
    
    DROP TABLE TestUser
    GO
    

    You can read more on error handling here: http://www.sommarskog.se/error-handling-I.html

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlinfomessageeventhandler.aspx

    http://geekswithblogs.net/mrnat/archive/2004/09/20/11431.aspx

    Hope this is helpful


    Krishnakumar S

    • Proposed as answer by Kalman TothModerator Wednesday, August 21, 2013 10:26 AM
    • Marked as answer by mds2907 Wednesday, August 21, 2013 10:29 AM
    Wednesday, August 21, 2013 9:20 AM

All replies

  • The messages from a stored procedure can be;

    1. Business Rule exceptions (in your case duplicate email) that has to be raised as an error message. This will terminate the execution of the procedure or continue based on the business rule implemented

    2. System / Database messages that also need to be passed to the calling application as an error message to log or trouble shoot the issue. This will definitley terminate the execution of the procedure.

    3. Informational messages passed to the calling application, like 'Registered Successfully', that the execution can be continued.

    The first and second category can be treated as an error message and raised as an error. But the third one, the informational message, this cannot be raised as an error. This can be done by one of the following methods.

    a. An output parameter from the procedure that set a value, say an integer value of 0, that indicate success and, handle this in application

    b. A select query of proper message returned to the calling application as a result set like, SELECT 'Registered Successfully' AS Message

    c. The error severity from 1 to 10 is considered as informational message by most of the data access APIs. So issue a RAISERROR having a severity between 1 to 10 with proper message.

    I think the simplest is the first one and the most intutive is the third. Data access APIs and classes like SQLClient, OLEDbClient etc supports a special event handler for processing informational messages. So this will be more meanigful.

    For example here is a skeleton code as an example:

    CREATE TABLE TestUser
    (
    EMail VARCHAR(100),
    Name VARCHAR(10)
    )
    GO
    
    CREATE PROCEDURE PR_SaveUser
    (
    @EMail VARCHAR(100),
    @Name VARCHAR(100)
    )
    AS
    	BEGIN
    
    		SET NOCOUNT ON;
    
    		IF EXISTS(SELECT 1 FROM TestUser WHERE EMail = @EMail)
    			BEGIN
    
    				-- Custom Error Message
    				RAISERROR ('User Already Exists', 16, 1);
    				RETURN 1;
    
    			END;
    
    		ELSE
    			BEGIN
    				
    				BEGIN TRY
    				
    					BEGIN TRANSACTION;
    					INSERT INTO TestUser VALUES (@EMail, @Name);
    					COMMIT TRANSACTION;
    
    					-- Severity 1 - 10 is informational Message;
    					RAISERROR ('User Registered Successfully', 10, 1);
    					RETURN 0;
    
    				END TRY
    				BEGIN CATCH
    					
    					IF @@TRANCOUNT > 0 ROLLBACK;
    
    					DECLARE @ErrMsg AS NVARCHAR(4000) = '',
    							@ErrSeverity AS INT = 0,
    							@ErrState AS INT = 0;
    
    					-- Server/ Database errors
    					SELECT @ErrMsg = 'Error in PR_SaveUser : ' + ERROR_MESSAGE(), 
    							@ErrSeverity = ERROR_SEVERITY(), 
    							@ErrState = ERROR_STATE();
    
    					RAISERROR (@ErrMsg, @ErrSeverity, @ErrState);
    
    				END CATCH
    			END;
    
    	END;
    GO
    
    -- Will succeed with info message 'User Registered Successfully'
    EXEC PR_SaveUser 'alice@acme.com', 'Alice';
    EXEC PR_SaveUser 'bob@acme.com', 'Bob';
    
    -- Will throw the custom error 'User Already Exists' and terminate the procedure
    EXEC PR_SaveUser 'alice@acme.com', 'Alice';
    
    -- Will throw 'Error in PR_SaveUser : String or binary data would be truncated.'
    EXEC PR_SaveUser 'kk@acme.com', 'Krishnakumar'; -- Name is VARCHAR(10)
    
    DROP PROCEDURE PR_SaveUser
    GO
    
    DROP TABLE TestUser
    GO
    

    You can read more on error handling here: http://www.sommarskog.se/error-handling-I.html

    http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlinfomessageeventhandler.aspx

    http://geekswithblogs.net/mrnat/archive/2004/09/20/11431.aspx

    Hope this is helpful


    Krishnakumar S

    • Proposed as answer by Kalman TothModerator Wednesday, August 21, 2013 10:26 AM
    • Marked as answer by mds2907 Wednesday, August 21, 2013 10:29 AM
    Wednesday, August 21, 2013 9:20 AM
  • Hi Krishnakumar S,

    Thanks for the reply. Thank you for giving such a nice example and various scenarios regarding SQL error handling. Your information has helped me  in getting a clear picture to a great extent which I was struggling to understand and I had browsed several articles and code examples regarding the same. But your info provided a clear understanding to me.

    Thanks Again !!! :)

    mds

    Wednesday, August 21, 2013 10:28 AM