I am a novice when it comes to Microsoft SQL Server things more completed (in my view of things) then a JOIN.

I use Microsoft Visual Studio Pro 2017 and Microsoft SQL Server 2016. I use a VB class to save data to a database table using a stored procedure.

The VB code snippet is as follows:

      Protected Function _SaveLiteral(ByVal _strLiteralId As String,
                              ByVal _blnWebAdminOnly As Boolean,
                              ByVal _blnShow As Boolean,
                              ByVal _fkstrLiteralType As String,
                              ByVal _strDescription As String,
                              ByVal _strLiteral As String) As Boolean

      Dim _cmdBaBLiteral As New SqlCommand
      Dim _objUserData As membershipUser = membership.getuser()

      Dim _intSQLErrorNumber As Int32 = 0
      Dim _intSQLErrorSeverity As Int32 = 0
      Dim _intSQLErrorState As Int32 = 0
      Dim _strSQLErrorProcedure As String = String.Empty
      Dim _intSQLErrorLine As Int32 = 0
      Dim _strSQLErrorMessage As String = String.Empty
      Dim _strUserName As String = String.Empty

      _objBaBAudit.objConnectionStringBabUtilityAdmin = _objConnectionStringBabUtilityAdmin
      _objBaBAudit.objConnectionStringBabUtilityMember = _objConnectionStringBabUtilityMember

            _cnnBaBLiteral.ConnectionString = _objConnectionStringBabUtilityAdmin
            _cmdBaBLiteral.CommandText = "bab_i_Literal"
            _cmdBaBLiteral.CommandType = CommandType.StoredProcedure
            _cmdBaBLiteral.Connection = _cnnBaBLiteral
            _cmdBaBLiteral.Parameters.Add("@strLiteralId", SqlDbType.NVarChar, 100).Value = _strLiteralId
            _cmdBaBLiteral.Parameters.Add("@blnWebAdminOnly", SqlDbType.Bit).Value = _blnWebAdminOnly
            _cmdBaBLiteral.Parameters.Add("@blnShow", SqlDbType.Bit).Value = _blnShow
            _cmdBaBLiteral.Parameters.Add("@fkstrLiteralType", SqlDbType.NVarChar, 25).Value = _fkstrLiteralType
            _cmdBaBLiteral.Parameters.Add("@strDescription", SqlDbType.NVarChar, 256).Value = _strDescription
            _cmdBaBLiteral.Parameters.Add("@strLiteral", SqlDbType.NVarChar, 4000).Value = _strLiteral
            _cmdBaBLiteral.Parameters.Add("@intSQLErrorNumber", SqlDbType.Int).Direction = ParameterDirection.Output
            _cmdBaBLiteral.Parameters.Add("@intSQLErrorSeverity", SqlDbType.Int).Direction = ParameterDirection.Output
            _cmdBaBLiteral.Parameters.Add("@intSQLErrorState", SqlDbType.Int).Direction = ParameterDirection.Output
            _cmdBaBLiteral.Parameters.Add("@strSQLErrorProcedure", SqlDbType.NVarChar, 128).Direction = ParameterDirection.Output
            _cmdBaBLiteral.Parameters.Add("@intSQLErrorLine", SqlDbType.Int).Direction = ParameterDirection.Output
            _cmdBaBLiteral.Parameters.Add("@strSQLErrorMessage", SqlDbType.NVarChar, 4000).Direction = ParameterDirection.Output


            If _objUserData Is Nothing Then
                  _strUserName = "Anonymous"
                  _strUserName = _objUserData.UserName
            End If

            _intSQLErrorNumber = _cmdBaBLiteral.Parameters.Item("@intSQLErrorNumber").Value

            If _intSQLErrorNumber > 0 Then
                  _intSQLErrorSeverity = _cmdBaBLiteral.Parameters.Item("@intSQLErrorSeverity").Value
                  _intSQLErrorState = _cmdBaBLiteral.Parameters.Item("@intSQLErrorState").Value
                  _strSQLErrorProcedure = _cmdBaBLiteral.Parameters.Item("@strSQLErrorProcedure").Value
                  _intSQLErrorLine = _cmdBaBLiteral.Parameters.Item("@intSQLErrorLine").Value
                  _strSQLErrorMessage = _cmdBaBLiteral.Parameters.Item("@strSQLErrorMessage").Value

                  _strMessage = "An error occurred."

                  If _objBaBAudit.SaveAudit("_SaveLiteral: " & _strLiteralId,
                                      "BabUtlity - LiteralData.vb - _SaveLiteral",
                                      _strSQLErrorMessage) Then
                  End If

                  Return False
            End If

                  If _objBaBAudit.SaveAudit("LiteralData.vb - _SaveLiteral: " & _strLiteralId,
                                      "BabUtlity - LiteralData.vb - _SaveLiteral",
                                      0) Then
                  End If

            Catch ex As Exception

                  _strMessage = _objBaBString.HtmlLineBreak(_strMessage) &

                  If _objBaBAudit.SaveAudit("_SaveLiteral: " & _strLiteralId,
                                      "BabUtlity - LiteralData.vb - _SaveLiteral",
                                      False) Then
                  End If

                  Return False
            End Try

            Return True

      End Function

My stored procedure is as follows:

      /****** Object:  StoredProcedure [dbo].[bab_i_Literal]    Script Date: 12/31/2017 2:53:47 PM ******/
      -- =============================================
      -- Author:      
      -- Create date: 3/25/2008
      -- Description:      Insert a literal row
      -- =============================================
      ALTER PROCEDURE  [dbo].[bab_i_Literal]
            -- Add the parameters for the stored procedure here
            @strLiteralId                  nvarchar(100),
            @blnWebAdminOnly            bit,
            @blnShow                  bit,
            @fkstrLiteralType            nvarchar(25),
            @strDescription                  nvarchar(256),
            @strLiteral                  nvarchar(4000),
            @intSQLErrorNumber            int = 0                  OUT,
            @intSQLErrorSeverity            int = 0                  OUT,
            @intSQLErrorState            int = 0                  OUT,
            @strSQLErrorProcedure            nvarchar(128) = ''      OUT,
            @intSQLErrorLine            int = 0                  OUT,
            @strSQLErrorMessage            nvarchar(4000) = ''      OUT
            -- SET NOCOUNT ON added to prevent extra result sets from
            -- interfering with SELECT statements.
            SET NOCOUNT ON;
          -- Insert statements for procedure here
            BEGIN      TRY
                  INSERT      Literal
                  VALUES            (@strLiteralId,
            END      TRY
            BEGIN      CATCH
                  SET      @intSQLErrorNumber = ERROR_NUMBER()
                  SET      @intSQLErrorSeverity = ERROR_SEVERITY()
                  SET      @intSQLErrorState = ERROR_STATE()
                  SET      @strSQLErrorProcedure = ERROR_PROCEDURE()
                  SET      @intSQLErrorLine = ERROR_LINE()
                  SET      @strSQLErrorMessage = ERROR_MESSAGE()
            END      CATCH

If the SQL CATCH is executed the stored procedure returns a -4 and the SQL error number returns 2601. This is expected. However, this may be handled in the VB code as if no error occurred since the VB Catch block is not executed. If I remove the SQL TRY/CATCH the VB Catch is block is executed.

Is this what normally happens?

On the MSDN website I found on page a sample of how to create an error stored procedure as follows:

      -- Verify that the stored procedure does not already exist.  
      IF OBJECT_ID ( 'usp_GetErrorInfo', 'P' ) IS NOT NULL  
          DROP PROCEDURE usp_GetErrorInfo;  
      -- Create procedure to retrieve error information.  
      CREATE PROCEDURE usp_GetErrorInfo  
          ERROR_NUMBER() AS ErrorNumber  
          ,ERROR_SEVERITY() AS ErrorSeverity  
          ,ERROR_STATE() AS ErrorState  
          ,ERROR_PROCEDURE() AS ErrorProcedure  
          ,ERROR_LINE() AS ErrorLine  
          ,ERROR_MESSAGE() AS ErrorMessage;  
      BEGIN TRY  
          -- Generate divide-by-zero error.  
          SELECT 1/0;  
      END TRY  
          -- Execute error retrieval routine.  
          EXECUTE usp_GetErrorInfo;  
      END CATCH;  

Since the VB code wants the values returned from the SQL ERROR_ values I don't believe the 'usp_GetErrorInfo' stored procedure will lessen the number of output values declared when the stored procedures is executed in order to have these values returned to the VB code.

Is this correct?

Any input the SQL or VB gurus might offer is most appreciated.

Much thanks,
David Bach
David BachSoftware DevelopmentAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Disclaimer first, I am not guru in either.

I think the paradigm is identical across layers (business or database). Exception once handled is never propagated (or bubbled up). This is the behaviour you are seeing when you comment try catch block in SQL Server, exception is thrown in VB code. Typically, if you plan to handle exception in SQL Server, then in your VB code just check for the output variables that you have initialised in stored procedure code. If their values are not initialised, your sp executed successfully otherwise not. Act accordingly in VB code.

Exception handling in database will be able to log detailed exception information in db table itself and is available locally for someone fixing it at db level.

Hope that throws more light.
David BachSoftware DevelopmentAuthor Commented:
Greetings Nitin:

Thank you for responding so quickly.

If I wrote my SQL stored procedure as follows which takes out the TRY/CATCH. I purposefully attempted to store a new row to the table with a duplicate key. The VB Try/Catch transferred to the Catch portion, however, I found the SQL ERROR routines returned NULL values.

/****** Object:  StoredProcedure [dbo].[bab_i_Literal]    Script Date: 12/31/2017 10:36:14 PM ******/

-- =============================================
-- Author:		
-- Create date: 3/25/2008
-- Description:	Insert a literal row
-- =============================================
ALTER PROCEDURE  [dbo].[bab_i_Literal]
	-- Add the parameters for the stored procedure here
	@strLiteralId			nvarchar(100),
	@blnWebAdminOnly		bit,
	@blnShow				bit,
	@fkstrLiteralType		nvarchar(25),
	@strDescription			nvarchar(256),
	@strLiteral				nvarchar(4000),

	@intSQLErrorNumber		int = 0						OUT,
	@intSQLErrorSeverity	int = 0						OUT,
	@intSQLErrorState		int = 0						OUT,
	@strSQLErrorProcedure	nvarchar(128) = ''			OUT,
	@intSQLErrorLine		int = 0						OUT,
	@strSQLErrorMessage		nvarchar(4000) = ''			OUT
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

    -- Insert statements for procedure here
	INSERT		Literal
		VALUES		(@strLiteralId,

	IF @@ERROR <> 0
			SET @intSQLErrorNumber = ERROR_NUMBER()
			SET @intSQLErrorSeverity = ERROR_SEVERITY()
			SET @intSQLErrorState = ERROR_STATE()
			SET @strSQLErrorProcedure = ERROR_PROCEDURE()
			SET @intSQLErrorLine = ERROR_LINE()
			SET @strSQLErrorMessage = ERROR_MESSAGE()

			IF @intSQLErrorNumber IS NULL
				SET @intSQLErrorNumber = 0

			IF @intSQLErrorSeverity IS NULL
				SET @intSQLErrorSeverity = 0

			IF @intSQLErrorState IS NULL
				SET @intSQLErrorState = 0

			IF @strSQLErrorProcedure IS NULL
				SET @strSQLErrorProcedure = ''

			IF @intSQLErrorLine IS NULL
				SET @intSQLErrorLine = 0

			IF @strSQLErrorMessage IS NULL
				SET @strSQLErrorMessage = ''


Open in new window

It seams the SQL ERROR routines do not contain the error information from the INSERT command.

Any clues as to why?

Much thanks,
David Bach
Nitin SontakkeDeveloperCommented:
Not having things set-up fully to test, I may not comment with confident. However, you can keep on ruling out the situations.

For example, first, in sp remove the code where you are making all ?SQLError? variables empty or 0 depending on their data type.

You can just select those all variables in the stored procedure and then execute stored procedure in query analyzer to see the you get expected output and then comment out select statement. That rules out any db side error.

In your VB code, I see all of the ?SQLError? variables declared but not actually passed as a parameter to the sp (in code posted originally). For output parameters you will need to pass on placeholder variables to hold values and passed from one layer to another.

Hope that should get you started.
Ganesh GuruduSenior ConsultantCommented:
I am not sure why you are doing soo much of code for catching Error message and Error Code

You can do like this. you can get all SQL related Errors code and Messages.

        Catch ex As SqlException
            Dim i As Integer
            For i = 0 To ex.Errors.Count - 1
                            "Index #" & i.ToString() & ControlChars.NewLine _
                    & "Message: " & ex.Errors(i).Message & ControlChars.NewLine _
                    & "LineNumber: " & ex.Errors(i).LineNumber & ControlChars.NewLine _
                    & "Source: " & ex.Errors(i).Source & ControlChars.NewLine _
                    & "Procedure: " & ex.Errors(i).Procedure & ControlChars.NewLine)
            Next i
        End Try

Open in new window

You can also do like this.
Catch sqlEx As SqlException When sqlEx.Number = <Error Code/10002>
     // Put whatever you want here

this URL may helps check.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David BachSoftware DevelopmentAuthor Commented:
Thank you Ganesh and Nitin! You both answered questions regarding my question.

I appreciate your time and patience in helping me.

David Bach
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.