Passing URL Parameters to Stored Procedures

Code On Time generator allows creating data controllers from the result set of a stored procedure. Some stored procedures use parameters in order to perform operations on the data. For example, the EmployeeSalesByCountry stored procedure in the sample Northwind database takes two parameters (@Beginning_Date and @Ending_Date) in order to filter the returned result by the two dates.

Let’s allow the controller to accept URL parameters to set the @Beginning_Date and @Ending_Date for the stored procedure, as shown in the picture.

The stored procedure definition can be seen below.

CREATE procedure dbo.[Employee Sales by Country] 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT    Employees.Country, 
        Employees.LastName, 
        Employees.FirstName, 
        Orders.ShippedDate, 
        Orders.OrderID, 
        "Order Subtotals".Subtotal AS SaleAmount
FROM Employees INNER JOIN 
    (Orders INNER JOIN "Order Subtotals" ON Orders.OrderID = "Order Subtotals".OrderID) 
    ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date

Let’s create the data controller with an SQL query that will take the parameters from the URL if present. Otherwise, it will use default values. Our script will specify defaults that allow all records to be seen. If necessary, the default values could be set to the same date in order to return zero records when URL parameters are not specified.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Click on the New Controller icon on the toolbar.

Enter a name for the controller.

Press OK to save. Right-click the new controller and press “Generate From SQL…”.

In the SQL script textbox, paste in the following script. The debug section is removed from the business rule when the application framework executes the script at runtime and declares the URL parameters.

-- debug
declare @Url_BeginningDate datetime,
        @Url_EndingDate datetime
-- end debug

declare @Beginning_Date datetime,
        @Ending_Date datetime

if (@Url_BeginningDate is not null)
    set @Beginning_Date = @Url_BeginningDate
else 
    set @Beginning_Date = '1970-01-01'

if (@Url_EndingDate is not null)
    set @Ending_Date = @Url_EndingDate
else 
    set @Ending_Date = '2000-01-01'

EXEC dbo.[Employee Sales by Country] @Beginning_Date, @Ending_Date

Press OK to generate the controller.

Next, let’s add the controller to a page. Right-click on the controller and press Copy.

Switch to the Pages tab in the Project Explorer. On the toolbar, press the New Page icon.

Give a name to the page and press OK to save.

Drop the new page to the right side of Home page node to place it second in the site menu.

Right-click on the page and press Paste to instantiate the controller as a data view on the page.

On the toolbar, press Browse. The page will open in your default browser. Note that all 809 items are displayed.

In the URL bar of the browser, replace everything after “.aspx” with the following:

?BeginningDate=1996-07-01&EndingDate=1996-08-01

Press Enter. Notice that only 17 items are displayed.

The next steps may be to create a Navigate action that redirects to this page and sets the URL parameters.