Saturday, 30 July 2011

Stored procedures

Stored procedures are essentially functions that you can create in the database and reuse. What's neat about them from a usability standpoint is that they can take input parameters and then return a result.

Stored procedures have many benefits, including:
  • Speed - Stored procedures are pre-compiled, so the execution plan doesn't have to be figured out each time they're called. This can result in a significant performance improvement.
  • Code reuse and abstraction - Stored procedures often involve complex code. It's nice that this code doesn't have to be rewritten over and over again and that even entry-level SQL programmers can make use of it with a simple stored procedure call. 
  • Security - Permissions can be granted for stored procedures while being restricted for the underlying tables. This allows the DBA to provide a method for SQL programmers and report writers to access and/or manipulate data in a safe way. 
  • Reduced traffic between client and server - Since the bulk of the query is already stored on the server and only the relatively short stored procedure call has to get sent, traffic to the server is decreased.
 CREATE PROCEDURE procedure_name
                @param data_type = default_value,
                @param data_type = default_value,
                @param data_type = default_value
                 AS
                -- statements for procedure here

Code Sample: StoredProcedures/Demos/StoredProc.sql

CREATE PROCEDURE SalesByCategory
 @CategoryName nvarchar(15),
 @OrdYear int = 1998
AS

SELECT ProductName, SUM(OD.Quantity * (1-OD.Discount) * OD.UnitPrice) AS TotalPurchase
FROM "Order Details" od, Orders o, Products p, Categories c
WHERE od.OrderID = o.OrderID
 AND od.ProductID = p.ProductID
 AND p.CategoryID = c.CategoryID
 AND c.CategoryName = @CategoryName
 AND DATEPART(year,OrderDate) = @OrdYear
GROUP BY ProductName
ORDER BY ProductName

--Call stored procedure
EXEC SalesByCategory 'Seafood', 1997
 
Must Read

Recursion is one of the most elegant programming constructsin my opinion. I have used it in dozens of situations and in severalprogramming languages. The basic concept of recursion is straightforward: agiven chunk of code calls itself until some boundary condition is reached. I'lldemonstrate you how to use recursion in T-SQL, using one of the classicexamples of recursion: the factorial calculation.

A factorial is the multiple of any number by all the lessernumbers down to two. For example, factorial(10) isequal to 10 * 9 * 8 * 7 * 6 * 5 * 4 * 3 * 2. (You could add * 1, but whybother?)

The following script implements the factorial algorithm:

CREATE PROCEDURE [dbo].[Factorial_ap]
(
@Number Integer,
@RetVal Integer OUTPUT
)
AS
DECLARE @In Integer
DECLARE @Out Integer
IF @Number != 1
BEGIN
SELECT @In = @Number – 1
EXEC Factorial_ap @In, @Out OUTPUT
SELECT @RetVal = @Number * @Out
END
ELSE
BEGIN
SELECT @RetVal = 1
END
RETURN
GO

Assuming that you want to calculate factorial(n), the procedure will call itself (n-2) times. SQL Server permits recursionto a depth of 32 calls, but at 13, you'll suffer arithmetic overflow. If youexpect to be calculating factorials for large numbers, then you should declarethe variable as BigInt rather than Integer. This willallow you to calculate factorial(20), whichincidentally is 2,432,902,008,176,640,000. The results increase in size soquickly that factorial(21) breaks this implementationas well.

As pretty as the factorial algorithm is, it's unlikely thatyou'll have much use for it in your everyday programming. Nevertheless, itconcisely illustrates the principle of recursion.