Calling a Stored Procedure

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Tuesday, February 19, 2013PrintSubscribe
Calling a Stored Procedure

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.

Creating a Stored Procedure

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind node, and press New Query.

Creating a new query for Northwind database.

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.

Adding the Business Rule

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.

Creating a new business rule for Orders controller.

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.

Viewing the Results

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.

The business rule returned the average value from the stored procedure and displayed a message next to the Freight field.

Enter a value below the displayed average and press OK. The order will be saved.