Return Value from a Stored Proc on Error

Return error message from stored procedure

Try to use TRY CATCH and catch your error like this:

BEGIN TRY
   delete from Test
    where  ID = @ID
END TRY
BEGIN CATCH
    SET @ErrorMessage  = ERROR_MESSAGE()
    SET @ErrorSeverity = ERROR_SEVERITY()
    SET @ErrorState    = ERROR_STATE()
    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
    BREAK
END CATCH

Getting the return value from stored procedure and using it in program

The SQL is more complicated than it needs to be - you could use

CREATE Procedure Check_Previous_Passwords
    @ua_pk uniqueidentifier,
    @IncomingPassword varchar(25)
AS
    SELECT COUNT(*)
    FROM User_Passwords up 
    WHERE up.ua_fk = @ua_pk
        AND @IncomingPassword = up.up_Password

and the VB which uses it needs to be a function so that it can return a value:

Option Strict On
' ...'
Public Function Check_Previous_Passwords(ByVal User As FoundationLibrary.User) As Boolean
    Dim isSamePassword As Integer
    Dim objCommand As New SqlCommand("Check_Previous_Passwords", DatabaseInterface_.Connection)
    objCommand.CommandType = CommandType.StoredProcedure
    objCommand.Parameters.Add(New SqlParameter With {.ParameterName"@ua_pk", .SqlDbType = SqlDbType.UniqueIdentifier, .Value = ua_pk_})
    objCommand.Parameters.Add(New SqlParameter With {.ParameterName = "@IncomingPassword", .SqlDbType = SqlDbType.VarChar, .Size = 25, .Value = ua_Password_})
    DatabaseInterface_.Open()
    isSamePassword = CInt(objCommand.ExecuteScalar)
    DatabaseInterface_.Close()

    User.ua_ResetPassword_ = (isSamePassword = 1)

    Return User.ua_ResetPassword_

End Function

I changed the AddWithValue parts to a version which works reliably. You should avoid AddWithValue - it will only bring you misery eventually: Can we stop using AddWithValue() already?

SQL Server output and return value in procedure

The return value of a stored procedure should always be of integer data type.
If you use a function, you could return other data types, but you can have only one value returned.

As you need two values out of the stored procedure, you could use two output parameters...

CREATE PROCEDURE info2 (@id int , @howMuch int OUTPUT, @name varchar(100) OUTPUT) AS

Stored procedure doesn't return correct value

Stored procedure can only return integers (ref).

You could return it from an output parameter, but I think you should create a function:

CREATE FUNCTION ReplaceHtmlEntities
    (@companyName  NVARCHAR(200))
RETURNS NVARCHAR(200)
AS
BEGIN
    DECLARE @newCompanyName NVARCHAR(200);

    SET @newCompanyName = @companyName;
    SET @newCompanyName = REPLACE(@newCompanyName,'&','&');
    SET @newCompanyName = REPLACE(@newCompanyName,'"','"');
    SET @newCompanyName = REPLACE(@newCompanyName,''','''');

    RETURN @newCompanyName;
END
GO

And this is how you can execute a function:

DECLARE @companyName nvarchar(200), @result nvarchar(200)
SET @companyName = N'Company name &'

SET @result = [dbo].[ReplaceHtmlEntities](@companyName)

SELECT  @result

Is returning an error code value from stored procedure good practice?

If you are using exceptions in the rest of your project, I'd say exceptions should be the way to go. Notice you have the RAISEERROR (http://msdn.microsoft.com/en-us/library/ms178592.aspx) SQL clause so you can raise exceptions within those stored procedures.

I believe the severity must be over 16 on RAISEERROR for it to be raised as an exception (you may want to check http://msdn.microsoft.com/en-us/library/ms164086.aspx). Exceptions should rise for any error that would work on a TRY/CATCH SQL block.

---

Related Topics