Validating in Stored Procedures, not VBA (1 Viewer)

[kleky]

Hello folks

I've really started to embrace Stored Procedure and thought it would be good to validate within them too.

I'd normally validate in a VBA function but I like the approach of having rules and business logic stored centrally within SQL Server.

I shall have tables used within different front ends, so why duplicate validation when, for example, inserting a new record?

My problem is this: is there a simple way to handle/trap the error produced in the stored procedure using the RAISERROR function? Presently I can only return the error in the error handler, which is:

ODBC--call failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]This is a test error 15/1! ahhhhh! (#50000)

Is this the best I can get?

Many thanks

 

[Rx_]

What API are you using to communicate with the SQL Server? (ODBC, SQL Native Client, ...)?
is your basic stored procedure format?

    BEGIN TRANSACTION;
    BEGIN TRY
    
    -- Code in your transaction here
    
    COMMIT TRANSACTION;
		
    END TRY BEGIN CATCH
        
        -- Rollback on error
        ROLLBACK TRANSACTION;

        -- Raise the error with the error message and error severity
        DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int;
        SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY();
        RAISERROR(@ErrMsg, @ErrSeverity, 1);

    END CATCH;

Errors raised either by the SQL Server Database Engine or the RAISERROR statement are not part of a result set. Errors are returned to applications through an error-handling mechanism that is separate from the processing of result sets.
Each database application programming interface (API) has a set of functions, interfaces, methods, objects, or structures through which they return errors and messages. Each API function or method typically returns a status code indicating the success of that operation. If the status is anything other than success, the application can call the error functions, methods, or objects to retrieve the error information.

Applications using APIs such as ActiveX Data Object (ADO) and OLE DB cannot generally distinguish between errors and messages. In Open Database Connectivity (ODBC) applications, messages generate a SQL_SUCCESS_WITH_INFO function return code, and errors usually generate a SQL_ERROR return code. The difference is most pronounced in DB-Library, in which errors are returned to the application error-handler function, and messages are returned to the application message-handler function.

http://msdn.microsoft.com/en-us/library/ms189583(v=sql.105).aspx
This describes the general differences between the API.

 

[kleky]

Thanks Rx, I see where you're going on this but I have no idea what API Access 2010 is. In fact, i'd like to learn about all these acronyms somehow.

I'm using ODBC PassThrough queries...if that helps?

I'd guess I'm using ADO or OLE DB so can't distinguish between ERRORS or MESSAGES? Would it be bad practice to trap the errors as part of validation? It doesn't sit right with me.

Example SP:

USE [Organisations]
GO
/****** Object:  StoredProcedure [dbo].[siNewAddress]    Script Date: 06/11/2013 14:23:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Ian Klek
-- Create date: 11/6/13
-- Description:	Creates a new Address row with required values
-- =============================================
ALTER PROCEDURE [dbo].[siNewAddress] 
	-- Add the parameters for the stored procedure here
	@OrgID int, 
	@CountryID int,
	@TypeID int,
	@Line1 varchar(80),
	@Line2 varchar(80),
	@Line3 varchar(80),
	@Town varchar(80),	--Line 4
	@City varchar(80),	--Line 5
	@County varchar(80),--Line 6
	@PostCode varchar(10), 
	@Website varchar(100)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO dbo.tblAddresses 
		(intFKAddresses_OrgID,intFKAddresses_CountryID,intFKAddresses_AddressTypeID,
		chvAddressesLine1,chvAddressesLine2,chvAddressesLine3,chvAddressesLine4,
		chvAddressesLine5,chvAddressesLine6,chvAddressesPostCode,chvAddressesWebsite)
	VALUES
		(@OrgID,@CountryID,@TypeID,@Line1,@Line2,@Line3,@Town,@City,@County,@PostCode,
		@Website)
	
	--RAISERROR('This is a test error 15/1! ahhhhh!',15,1)
END

 

[Rx_]

Before checking your code: This is in a book - thought it might be interesting to quote:
The @@ERROR Function
After every command in a stored procedure, you can use the @@ERROR function to query whether
an error has occurred. If the function returns a value not equal to 0, the preceding command triggered
an error. @@ERROR always refers only to the immediately preceding command.
The @@ERROR function is used in the following procedure:

 CREATE PROCEDURE PriceIncrease @Percentage REAL
AS
        SET NOCOUNT ON
        IF @Percentage IS NULL
            RETURN 0
 
        -- Price increase
        UPDATE tblProducts SET SalesPrice = SalesPrice * (1 + 
@Percentage)        IF @@ERROR > 0
        BEGIN
            SELECT 'Price increase could not be carried out!'
            RETURN 1
        END
        SET NOCOUNT OFF
        SELECT 'Price increase completed successfully!'
RETURN 0

When successful, this example procedure returns a value of 0. If unsuccessful, the returned value
is 1. System stored procedures usually return a value of 0 indicating success, unless otherwise
documented. The value returned with RETURN can only be read with an ADO program

 

[kleky]

Thanks, I could probably get this to work but it just seems like an untidy work-around.

Is there a command to drop out of the procedure if, for example, a variable was null?

 

[Rx_]

Lets just say that a SP will run more like a On Error Resume Next
It is what makes me a big fan of VBA instead of VBscript.

Won't say it can't be done. The rollback is necessary if only part of the transaction was completed when an error happens in-between.

 

[kleky]

I'm thinking to keep it simple...something like below, then access would simply display the error and stop.

-- validation condition 1
IF x > y 
   BEGIN
      RAISERROR(....)
      RETURN  --sp exit immediately
   END
ELSE
   BEGIN
      ..perform action
   END

-- validation condition 2
....

 

[kleky]

So this would work (see below). I would then use a function in VBA to return the error message in the square brackets if it contains 'ActionValidation'. What do you think? Validation now centralised in SQL server, allowing easy sight and maintenance, especially if used by more than one entity.

SET NOCOUNT ON;
	
	DECLARE @DeletingType varchar(15)
	
	
	SET @DeletingType = (
		SELECT at.chvAddressType
		FROM dbo.tblAddresses a INNER JOIN dbo.tbkAddressType at
		ON a.intFKAddresses_AddressTypeID = at.intPKAddressTypeID
		WHERE a.intPKAddressesID = @AddressID)
    
    
	IF @DeletingType = 'Physical' 
		BEGIN
		RAISERROR('ActionValidation[Not allowed to delete the physical address. This is fixed.]',18,1)
		RETURN
		END
	ELSE
		DELETE tblAddresses
		WHERE intPKAddressesID = @AddressID