SQL business rules allow server-side code to be executed in response to certain conditions. In addition to executing anonymous SQL code blocks, business rules can also be used in order to trigger a stored procedure.
Let’s create a stored procedure in the Northwind database that will calculate the average value of Freight for a specified customer. Then, a business rule will be added that will be triggered before every Update and Insert command in the Orders controller, and will use the stored procedure to verify that the Freight does not exceed the average freight. If it does, a message will be displayed and the command will be canceled.
Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind node, and press New Query.
Insert the following query:
create procedure sp_ValidateFreight -- inputted customer @CustomerID nvarchar(5), -- returned average freight @AverageFreight money output as begin select @AverageFreight = AVG(Freight) from Orders where CustomerID = @CustomerID end go
On the toolbar, press Execute to create the stored procedure.
Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Business Rules node, and press New Business Rule.
Assign the following values:
Property | Value |
Type | SQL |
Command Name | Update|Insert |
Phase | Before |
Script |
declare @AvgFreightOfOrders money -- execute stored procedure exec sp_ValidateFreight @CustomerID, @AverageFreight = @AvgFreightOfOrders output -- check the freight if @AvgFreightOfOrders is not null and @AvgFreightOfOrders < @Freight begin -- prevent actual Update or Insert from happening set @BusinessRules_PreventDefault = 1 -- show an alert next to the Freight field set @Result_Focus = 'Freight,The freight exceeds the average' + ' of $' + CONVERT(varchar(12), @AvgFreightOfOrders, 1) + ' of previous orders.' end |
Press OK to save the business rule.
On the toolbar, press Browse. When generation is complete, navigate to the Customers page, select a customer, and create a new order. Enter a very high value for Freight, and save the record. The save will be canceled and a message will be displayed next to Freight.
Enter a value below the displayed average and press OK. The order will be saved.