I use stored procedures for a lot of the data access I do within the applications I develop. Overall I like using stored procedures for data access, however, at times it makes “things” more difficult.
One of the “things“ that it makes more difficult is using optional parameters. In dynamic SQL we can handle optional parameters easily, simple don't append parameters that aren't needed. With stored procedures it isn't quite as straight-forward. My Optional Parameters in SQL Server Search queries post provides a solution.
Another task that is more difficult when using stored procedures is sorting. In many instances we want to be able to pass in the field that the records should be sorted on. Often times this is accomplished by using nested IF/ELSE statements. Although nested IFs do address the problem it also introduces a lot of duplication which we all know is bad. Fortunately there is a better way. Rather then introducing nested IF/ELSE statements to control the ORDER BY of your query use a CASE statement in the ORDER BY of your query.
CREATE PROCEDURE ap_OrderSearch
(
@SortBy varchar(50)
)
AS
select * from orders
ORDER BY
CASE
WHEN @SortBy = 'customerid' THEN CustomerID
WHEN @SortBy = 'shipname' THEN ShipName
WHEN @SortBy = 'shipaddress' THEN ShipAddress
END,
CASE
WHEN @SortBy = 'orderdate' THEN OrderDate
WHEN @SortBy = 'shippeddate' THEN ShippedDate
END,
CASE
WHEN @SortBy = 'orderid' THEN OrderID
WHEN @SortBy = 'employeeid' THEN EmployeeID
END
The above query returns records from the Orders table in the Northwind database. It accepts one parameter which is used to specify the field to sort the results by. The fields are grouped into 3 separate CASE statements by their data type to avoid conversion errors during the execution of the query.