3.10 Input Parameters

We now have a stored procedure which returns the details of available products based on the system date. The application development team tell us that they are also working on product administration screens for the business department. The business users would like to be able to enter a future date range and see all products that will become available after that future date.

Requirement: the business users will view a list of products based on date criteria that they specify

There are several choices available to the database developer. One option would be to remove all criteria from the get_product stored procedure and send every product back to the client applications. The application developers would be responsible for filtering the data on the client side. But this scenario is exactly the kind of performance-sapping situation that stored procedures avoid - we don't want to send any more records across the network than we have to. Instead, we use the power of input parameters.

We will recreate the stored procedure to allow the calling application to pass in a date argument.

In the listing shown, the original procedure is "altered" using the alter keyword - otherwise SQL Server won't let you issue the create procedure statement. The new stored procedure accepts one input parameter. An input parameter is a Transact-SQL variable: like all variables, it starts with '@' and has a datatype. By specifying the datetime datatype, this allows the calling application to pass in a date variable to the procedure.

Note that the where clause in the SQL statement is using the input parameter.

alter procedure get_product

(

@idtStartDate datetime

)

as

 

select product_id, product_name, start_date
from product
where start_date >= @idtStartDate

To test the procedure, we can call it passing in a future date range. The next listing shows two ways to call the stored procedure.

exec get_product '1 Jan 2009'

 

exec get_product

  @idtStartDate = '1 Jan 2009'

The first stored procedure call simply passes in the date.

The second syntax calles the procedure but this time uses a named parameter to pass the date argument. It's a good practice to use named parameters particularly when a procedure accepts more than one input parameter. In practice, many developers use the first syntax and rely on getting the sequence right - less typing!