Intro

Stored procedure are precompiled collections of SQL statements that can be called. The usual way for applications to interface with SQL Server is to either send SQL statements to SQL Server or to call stored procedures on SQL Server. Here are some basic benefits of stored procedures:

Stored procedures are available to MySQL 5.0+.

CREATE PROCEDURE

Here is the syntax to CREATE PROCEDURE.

CREATE PROC[EDURE] ProcedureName [ ;GroupNumber]
    [{@parameter DataType} [VARYING] [= Default] [OUTPUT]][ ,...]
[WITH {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}]
[FOR REPLICATION]
AS SQLStatement

A stored procedure can be created for three contexts:

Here is some info regarding CREATE PROCEDURE:

EG CREATE PROCEDURE

This example outputs an output parameter, result set, and a return code.

USE Northwind
GO
IF EXISTS(SELECT name FROM sysobjects
          WHERE name='OrderSummary' AND type='P')
DROP PROCEDURE OrderSummary
GO
CREATE PROCEDURE OrderSummary @MaxQuantity int OUTPUT
AS
--This SELECT makes a result set.
SELECT Ord.EmployeeID, SUMSales=SUM(OrDet.UnitPrice * OrDet.Quantity)
FROM Orders AS Ord
     JOIN [Order Details] AS OrDet ON (Ord.OrderID=OrDet.OrderID)
GROUP BY Ord.EmployeeID
ORDER BY Ord.EmployeeID

SELECT @MaxQuantity = MAX(Quantity) FROM [Order Details] --The output parameter.
RETURN (SELECT SUM(Quantity) FROM [Order Details])  --The return code.
GO

See the results at EG EXECUTE.

EG CREATE with cursor

This example takes an input and outputs a variable whose data type is cursor.

USE pubs
IF EXISTS (SELECT name FROM sysobjects 
           WHERE name='titles_cursor' and type='P')
DROP PROCEDURE titles_cursor
GO
CREATE PROCEDURE titles_cursor
    @SomeTitle as varchar(80) = 'The%',
    @titles_cursor CURSOR VARYING OUTPUT
AS
SET @titles_cursor=CURSOR
FORWARD_ONLY STATIC FOR
SELECT title
FROM titles
WHERE title LIKE @SomeTitle
OPEN @titles_cursor
GO

See the results at EG EXEC with cursor.

EG CREATE with default values

Use default values to create stored procedures with default, optional, or invalid parameters. If a stored procedure provides no default value, and the procedure is called without providing all values, than a system error occurs.

CREATE PROCEDURE insMe
@first int, --Required
@second int = 2, --Default, ie optional
@third int = 3 --Default, ie optional
IF @first = 99  --Invalid value
BEGIN
    PRINT 'ERROR: first cannot be 99'
    RETURN
END
INSERT INTO event (EventTypeID_FK, Name, UserID_FK, DateFired)
VALUES (@EventTypeID_FK, @Name, @Category, @SubCategory, GETDATE())
--Note that 4th entry (DateFired) is not paramaterized.

See the results at EGEXECwithdefaultvalues.

EXECUTE

Here is the syntax to EXECUTE a procedure:

[[EXEC[UTE]] 
{[@return_status = ] 
    {procedure_name [;GroupNumber] | @procedure_name_var}
} 
[[@parameter = ] {value | @variable [OUTPUT] | [DEFAULT]] [ ,...n ] 
[WITH RECOMPILE]

Note that for output parameters, the assignment operator (=) actually passes values from left to right instead of the usual right to left.

Here is the syntax to EXECUTE a character string:

EXEC[UTE] ({@string_variable | [N]'tsql_string'} [+ ...])
A stored procedure is optimized under these conditions:

Here are some notes regarding executing and using stored procedures:

EG EXECUTE

This example assumes that EG CREATE PROCEDURE was run. This demonstrates receipt of an output parameter, result set, and a return code.

DECLARE @OrderSum INT     --Receives the return code.
DECLARE @LargestOrder INT --Receives the output parameter.
EXEC @OrderSum = OrderSummary, @MaxQuantity = @LargestOrder OUTPUT
--The result set outputed and is usually accepted by the DB API.
PRINT 'The largest single order was: ' + CONVERT(CHAR(6), @LargestOrder)
PRINT 'The sum of quantities ordered was: ' + CONVERT(CHAR(6), @OrderSum)
GO

Here are the results:

EmployeeID  SUMSales             
----------- --------------------- 
1           202143.7100
2           177749.2600
3           213051.3000
4           250187.4500
5           75567.7500
6           78198.1000
7           141295.9900
8           133301.0300
9           82964.0000

(9 row(s) affected)

The largest single order was: 130   
The sum of quantities ordered was: 51317

EG EXEC with cursor

This example assumes that EG CREATE with cursor was run. This demonstrates the receipt of a cursor.

DECLARE @MyCursor cursor
DECLARE @TheTitle varchar(80)
EXEC titles_cursor
    @SomeTitle = '%Cooking%', 
    @titles_cursor = @MyCursor OUTPUT
FETCH NEXT FROM @MyCursor
INTO @TheTitle
PRINT 'Title: ' + @TheTitle
WHILE (@@FETCH_STATUS = 0)
BEGIN
   FETCH NEXT FROM @MyCursor
   INTO @TheTitle
   PRINT 'Title: ' + @TheTitle
END
CLOSE @MyCursor
DEALLOCATE @MyCursor
GO

Here are the results:

Title: Cooking with Computers: Surreptitious Balance Sheets
Title: Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean
Title: The Psychology of Computer Cooking
Title: The Psychology of Computer Cooking

EG EXEC string

Of course, you can do all sorts of fancy coding to generate the string used by the EXEC statement.

EXEC ('SELECT title FROM titles')
--The line above is just like the line below.
SELECT title FROM titles

Here are the results:

title
----------------------------------------------------------------
But Is It User Friendly?
Computer Phobic AND Non-Phobic Individuals: Behavior Variations
Cooking with Computers: Surreptitious Balance Sheets
Emotional Security: A New Algorithm
Fifty Years in Buckingham Palace Kitchens
Is Anger the Enemy?

(6 row(s) affected)

EG EXEC with default values

This example assumes that EG CREATE with default values was used.

EXEC insMe
--Fails since 1st parameter was required
EXEC insMe 12, 61, 45
--All parameters provided
EXEC insMe 6, @third = 77
--1st and 3rd parameter provided

EG SET NOCOUNT ON

You will often have to SET NOCOUNT ON because some of the intermediate steps return silly lines like "13 rows affected" and a consumer of the stored procedure might not continue with the rest of the stored procedure.

CREATE PROCEDURE sp_updAppointment
    @AppointmentID int,
    @Completed varchar(3) = 'xxx', 
    @Note varchar(500)
AS
SET NOCOUNT ON
IF @Completed='xxx' --This 'breaks' cuz of the True or False returned.
    BEGIN
        UPDATE Appointment
        SET Note=@Note
        WHERE AppointmentID=@AppointmentID
    END
ELSE
    BEGIN
        UPDATE Appointment
        SET
            Completed=@Completed, 
            Note=@Note
        WHERE AppointmentID=@AppointmentID
    END
SET NOCOUNT OFF
GO

Page Modified: (Hand noted: 2007-10-12 22:05:44Z) (Auto noted: 2007-11-17 06:44:27Z)