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:

  • They centralize logic on the server. The stored procedures can modularly used by different apps.
  • They protect users from the details of the execution. They only need to call upon the name instead of generating and sening collections of SQL statements.
  • They perform well because once a stored procedure is run, its execution plan is cached and it does not have to be recompiled.
  • They provide additional security since users can be permitted to execute a procedure even if they don't have permissions to execute some of the statements within the procedure.
  • They can accept and return parameters. A stored procedure returns data in up to four ways:
    • Output parameter . This is a scalar value or a cursor.
    • Global cursor . This can be referenced outside the procedure.
    • Return code . This is always an integer.
    • One or more result sets . One result set per SELECT statement.

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:

  • A permanent stored procedure is for use by any process that may call upon it.
  • A local temporary stored procedure is for temporary use within a the session of a user. The name of these stored procedures have this syntax: #Name.
  • A global temporary stored procedure is for temporary use within all the session of all users. The name of these stored procedures have this syntax: ##Name.

Here is some info regarding CREATE PROCEDURE:

  • ;GroupNumber signifies a procedure in a collection of procedures with the same name. This is so DROP PROCEDURE can drop the whole group.
  • A max of 2,100 parameters is allowed. There are no data type restrictions on parameters except for cursor , which can only be used as an OUTPUT. The VARYING keyword must also be used.
  • The max size for a stored procedure is 128 MB (65,025 characters in SQL Server 7).
  • The CREATE PROCEDURE statement cannot be combined with other T-SQL statements in batch, i.e. this is a top dog statement. However stored procedures may be nested, i.e. one stored procedure may call another. The nesting may be no more than 32 levels deep (16 in SQL Server 7). The current nesting level is stored in the @@NESTLEVEL global variable.
  • CREATE PROCEDURE cannot use the following statements:
    • CREATE DEFAULT
    • CREATE PROCEDURE
    • CREATE RULE
    • CREATE TRIGGER
    • CREATE VIEW
  • Wildcards can be used to specify the default values for parameters.

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:
  • The first time the stored procedure is used after a SQL Server restart.
  • The intrinsic stored procedure sp_recompile marks all stored procedures that use a particular table so that they will optimize upon their execution.
  • An underlying table used by the stored procedure changes.
  • The WITH RECOMPILE clauses are used with the EXECUTE command.

Here are some notes regarding executing and using stored procedures:

  • The EXEC keyword is not needed if the stored procedure is the first statement in a batch.
  • Stored procedures cannot be used as a table in SQL statements the way a function can!
  • All parameter values must be given in order unless the @Parameter = Value syntax is used.
  • The default value for ParameterA is used if @ParameterA = value is not listed or @ParameterA = DEFAULT is listed.
  • The sp_recompile system stored procedure can force a procedure to recompile on its next run.

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


GeorgeHernandez.comSome rights reserved