8.            Stored Procedures

SQL Server allows the programmer to write programs which can be executed repetitively with a simple instruction to the server. The programs are called Stored Procedures and consist of a series of Transact-SQL commands with structures to control program flow, receive parameters and return values and other features representing familiar programming principles.

One advantage of Stored Procedures is that they are executed on the server, performing a series of actions, before returning the results to the client. This allows a repetitive series of actions to take place with minimum network traffic and can considerably improve performance in many cases.

Security permissions can keep the underlying data hidden from the programmer to aid in more complex security requirements. For example, a stored procedure could be used to add bank account details to the database with access to the underlying table denied to the user.

Stored Procedures also have direct access to server resources and can call programs residing on the server to integrate with other systems or parts of the computer infrastructure.

Stored Procedures are created with the CREATE PROCEDURE command:

CREATE PROCedure [owner.]procedure_name[;number]

   [(parameter1 [, parameter2]...[parameter2100])]

[{FOR REPLICATION} | {WITH RECOMPILE}

   [{[WITH] | [,]} ENCRYPTION]]

AS sql_statements

Local and Global Temporary procedures can be created by prefixing the procedure name with a # or ## respectively.

SQL Enterprise Manager can be used to create and maintain stored procedures instead of using the CREATE, ALTER, and DROP PROCEDURE statements.

Creating a Stored Procedure

Stored Procedures may be renamed with the sp_rename system procedure.

Executing a Stored Procedure

[[EXECute]

{[@return_status =]

   {[[[server.]database.]owner.]procedure_name[;number] |

      @procedure_name_var}

   [[@parameter_name =] {value | @variable [OUTPUT]

      [, [@parameter_name =] {value | @variable [OUTPUT]}]...]

   [WITH RECOMPILE]

Stored procedures may perform an action or sequence of actions and return a single value or a result set. To execute a stored procedure immediately you can use the SQL Query Analyser tool. Type in the keyword EXECUTE followed by the procedure name and any parameters. The result or result set is displayed in the Result window:

EXECUTE stpgetauthors

Executing a Stored Procedure

Stored procedures normally return text messages indicating how many records have been selected along with other information. This can be suppressed by issuing the SET NOCOUNT ON command at the beginning of the stored procedure.

Stored procedures can also be executed from inside triggers or other stored procedures using the EXECUTE command. This is useful as common code can be placed out into a stored procedure for software reuse. Nesting and recursion is allowed down to 32 levels and the @@NESTLEVEL system variable indicates how many levels down the application has passed.

The EXECUTE command can also parse a string or a variable to execute code that can vary according to the context. The following example will select from a table specified in a local variable:

DECLARE @tname varchar(20)

SELECT @tname='authors'

EXECUTE ('select * from ' + @tname)

Multiple commands can be executed with the EXECUTE command:

EXECUTE( 'set nocount on;' + 'execute stpgetauthors' )

The procedure name may even be placed inside a variable so that automated tasks can be performed from a table:

DECLARE @pname varchar(20)

SELECT @pname='byroyalty'

EXECUTE @pname 40

One very important feature of Stored Procedures is that a procedure on a remote server may be run simply by specifying the server name in the procedure execute command. The remote server needs to be defined by the SQL administrator so that the servers can communicate but there is no need to log onto the second server as the local server will handle the communication.

Passing Parameters

Stored procedures can accept parameters and these are held in variables preceded with an @ symbol. These variables need their type defined explicitly in the stored procedure.

The following example accepts a parameter and returns a results set with the Authors selected by Surname according to the parameter passed.

CREATE PROCEDURE stpgetauthors

   @surname varchar(30)

 AS

BEGIN

   SELECT * FROM authors

      WHERE au_lname LIKE @surname

END

The parameter is passed to the procedure as follows:

execute stpgetauthors '[a-d]%'

Be careful when using SELECT * in a stored procedure as the fields are stored when the procedure is created or altered and may not reflect recent changes to the table structure.

Procedures can be created with default values for the parameters if none are entered by the user. The following example defaults the parameter to null and causes an error message if no parameter is passed to the function.

CREATE PROCEDURE stpgetauthors

   @surname varchar(30)=null

 AS

BEGIN

   IF @surname = null

   BEGIN

      RAISERROR( 'No selection criteria provided !', 10, 1)

   END

   ELSE

   BEGIN

      SELECT * FROM authors

         WHERE au_lname LIKE @surname

   END

END

A stored procedure may have more than one parameter declared and values are passed to the procedure in the order that they are declared:

CREATE PROCEDURE stpMathTutor @x int =1, @y int =1 AS

BEGIN

   …

END

The procedure may be executed with values of 2 for x and 3 for y as follows:

EXECUTE stpMathTutor 2,3

Missing out the second parameter with the execute will cause the variable to take up the default value and so the following example will run the procedure with x as 2 and y as the default value of 1.

EXECUTE stpMathTutor 2

Parameters may also be declared explicitly in the Execute command allowing for them to be specified independently of the order in which they have been declared. The following example gives y a value of 3 and leaves x undefned to take the default value of 1:

EXECUTE strMathTutor @y = 3

 

Returning a Value

Stored procedures also have the ability to return a value. This is done by using the return command in the procedure:

CREATE PROCEDURE stpMathTutor

   @x int =1 ,

   @y int =1

AS

BEGIN

   RETURN @x + @y

END

The value is returned by assigning the procedure to the variable as follows:

DECLARE @equals int

EXECUTE @equals = stpMathTutor 2,3

SELECT @equals

SQL Server will default the return value to zero. The returned values are typically used to return a status flag from the stored procedure with a non-zero value usually indicating failure during processing.

Returned values are difficult to access using ODBC their use is recommended only to return a success or failure of the stored procedure when communicating with other stored procedures.

Output Parameters

Values may also be returned into an output parameter by a stored procedure in a similar fashion to other programming languages returning a value by reference. This is achieved by including the 'output' command after the output parameter in the procedure definition.

ALTER PROCEDURE stpMathTutor

   @result int output,

   @x int =1 ,

   @y int =1

AS

BEGIN

   set @result = @x + @y

END

The returned value from a procedure can be stored in a variable for later use in the calling procedure or trigger. The variable is called in the command line with a “output” modifier:

EXECUTE stpMathTutor @equals output, 2, 3

Executing the Math Tutor

Program Structures

Transact SQL is primarily a set based language designed for processing sets of data using SQL statements. The language does contain control of flow structures similar to other programming languages.

The BEGIN…END statements are used to create a statement block around a series of Transact SQL statements.

BEGIN
    {sql_statement | statement_block}
END

The IF…ELSE structure is used extensively inside triggers and stored procedures.

IF Boolean_expression
      {sql_statement | statement_block}
[ELSE [Boolean_expression]
      {sql_statement | statement_block}]

 

The structure can use a SELECT statement to perform complex interrogations on data:

IF (SELECT SUM(qty) FROM inserted) > 500

BEGIN

   …

END

Remember the BEGIN…END structures around blocks of code otherwise only the first line is taken as part of the program flow.

WHILE Boolean_expression
      {sql_statement | statement_block}
      [BREAK]
      {sql_statement | statement_block}
      [CONTINUE]

The WHILE statement can be used to perform a loop to process a cursor for example. This might be useful when complex sets of different updates and actions need to be performed for each record in a table or when a server cursor is processed.

GOTO can be useful in controlling program flow. A label is defined in the code by placing a line with a label name and a colon and the GOTO command will move program flow to the label. This is especially useful with complex triggers and stored procedures where rollback and updates need close control:

IF (SELECT SUM(qty) FROM inserted) = 0

   GOTO noprocessing

   …

noprocessing:

   …

A stored procedure can be terminated at any time with the RETURN statement that returns an integer value (default is zero) to the calling program.

Comments can be placed in a stored procedure either with /* */ delimiters or with a double hyphen (--) to make the rest of a line into a comment.

/* Test the quantity */

IF @quantity = 0

   RETURN (-1) -- No processing required.

Local Variables

DECLARE @variable_name datatype

   [, @variable_name datatype...]

Local variables are used to store values within Transact-SQL. They must be declared before use and a datatype assigned.

The SELECT statement is then used to assign values to the variables.

SELECT @variable = {expression | select_statement}

   [, @variable = {expression | select_statement}...]

[FROM table_list]

[WHERE search_conditions]

[GROUP BY clause]

[HAVING clause]

[ORDER BY clause]

Values can be assigned in a similar fashion to most programming languages:

DECLARE @xvalue int

SELECT @xvalue = 22

Values can be determined by a SELECT statement which queries the database and returns a single value:

DECLARE @titleqty int

SELECT @titleqty = (SELECT SUM(qty)

   FROM sales WHERE title_id = @titleid)

The SET statement can be used instead of SELECT to assign a value to a variable:

DECLARE @dialcountry varchar(20)

SET @dialcountry =

   CASE @dialprefix

      WHEN '44' THEN 'UK'

      WHEN '01' THEN 'USA'

      ELSE 'OTHER'

   END

If the SELECT command used with a local variable as the last line of a stored procedure then the value of the variable is returned as a one record results set to the calling application:

SELECT @xvalue AS x, @yvalue AS y

System Variables

System variables exist which are automatically determined by SQL Server and do not have to be declared. These are always available and indicate a variety of values.

For example:

@@error                      Error number

@@identity                   Latest identity value of newly inserted record

@@language                Language currently in use

@@max_connections    Maximum connections allowed to the server

@@rowcount                Number of records affected by last command

@@rowcount                Number of rows affected by last statement

@@servername                        SQL Server name

@@version                   Version number of SQL Server

Other system information is returned from scalar functions:

DB_NAME()                  Database Name

SUSER_SNAME()         NT User Name

USER_NAME()              SQL Server User Name

All of these system variables can be used as required within any Transact SQL code as shown in the following example:

CREATE PROCEDURE stpserverinfo AS

select db_name(), user_name(),suser_sname(), @@servername, @@max_connections, @@version, getdate()

Scalar Functions

Scalar functions can also be used to perform an operation and return a single value. There are many examples some of which are listed below:

Mathematical and trigonometric functions:

·                  abs(-22.33)

·                  pi()

·                  sin(30)

·                  cos(30)

·                  tan(30)

·                  rand(22)

·                  round(3.4456,2)

Date functions:

·                  datepart(yyyy,getdate())

·                  year(@datevalue)

·                  dateadd( yyyy, 2, @datevalue)

·                  datediff( yy, @datevalue,getdate() )

·                  month( @datevalue )

String functions:

·                  left('abc123',3)

·                  ltrim('   abc')

·                  replace( 'abc','b','z' )

·                  soundex('abc' )

·                  substring( 'abc',2,1 )

·                  upper( 'abc' )

Miscellaneous:

·                  cast('abc' as varchar(5))

·                  convert(int, 22.33)

·                  columnproperty( object_id('authors'),'city', 'allowsnull')

·                  isnull( @xvalue,0,1 )

Look at the entry on 'scalar functions' in the online books for more information.

CASE Expression

CASE expression

   WHEN expression1 THEN expression1

   [[WHEN expression2 THEN expression2] [...]]

   [ELSE expressionN]

END

The CASE expression is very useful for assigning different values according to an expression for each record of a table. The following example will evaluate a description of the Authors contract status as a field in the results set according to the defined conditional rules:

SELECT *,

   'Contract Status' = CASE

      WHEN contract = 1 THEN 'Contracted'

      WHEN contract = 0 THEN 'No Contract'

      END

   FROM authors

The CASE expression can be used anywhere where an expression is required including in an Update statement to set values into a field.

This sophisticated example, shown blow, is taken from the SQL On-Line reference and shows a SELECT statement used within a CASE to change the expression shown:

SELECT a.au_lname Surname, a.au_fname Forename,

   "Royalty Category" =

   CASE

      WHEN (SELECT AVG(royaltyper) FROM titleauthor ta

         WHERE t.title_id = ta.title_id) > 60

         THEN 'High Royalty'

      WHEN (SELECT AVG(royaltyper) FROM titleauthor ta

         WHERE t.title_id = ta.title_id)

         BETWEEN 41 and 59

         THEN 'Medium Royalty'

      ELSE 'Low Royalty'

   END

   FROM authors a, titles t, titleauthor ta

   WHERE a.au_id = ta.au_id AND ta.title_id = t.title_id

   ORDER BY 1,2,3

Cursors

Stored procedures often need to process each record in a table and perform an action. For example a housekeeping program might run through all the new orders in a sales database and send email messages to the account manager in instances where the delivery date is more than five days from the date of order.

Cursors allow for the selection of the records in a stored procedure and the sequential processing of each record. Scrollable cursors also allow movement forwards and backwards through the table.

DECLARE cursor_name CURSOR
[ LOCAL | GLOBAL ]
[ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
[ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
[ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]

The cursor must first be declared before the FETCH command can be used to move up and down the cursor.

FETCH [[NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n] FROM] cursor_name

[INTO @variable_name1, @variable_name2, ...]

The @@FETCH_STATUS variable is set to zero after a successful FETCH and should always be checked before processing. A value of -1 indicates that the results set has been exceeded and -2 indicates that the cursor record is no longer a member of the original table.

Cursors are relatively slow and should not be used if more traditional set based processing is possible. Complex expressions can be created with the CASE expression within a SELECT statement and should be used in preference to a cursor whenever possible.

The following example illustrates the use of a cursor to process the records in a table one by one. The cursor is created from a SELECT statement and the OPEN command used to open the cursor. The value are FETCHed into variables that have already been defined and a WHILE loop used to process each record. The logic for each record is contained in the loop and mails a simple message.

 

CREATE PROCEDURE cursortest AS

 

DECLARE @id varchar (12)

DECLARE @firstname varchar(40)

DECLARE @surname varchar (40)

DECLARE @message varchar (80)

 

DECLARE curAuthors CURSOR LOCAL

   FOR SELECT au_id, au_fname, au_lname

   FROM authors WHERE contract = 1

   FOR READ ONLY

 

EXECUTE master..xp_startmail

 

OPEN curAuthors

FETCH NEXT FROM curAuthors INTO @id, @firstname, @surname

WHILE (@@fetch_status <> -1)

BEGIN

   IF (@@fetch_status <> -2)

   BEGIN

      SELECT @message = @id + @firstname + @surname

      EXECUTE master..xp_sendmail 'stamati crook', @message

   END

   FETCH NEXT FROM curAuthors INTO @id, @firstname, @surname

END

DEALLOCATE curAuthors

Take care to CLOSE or DEALLOCATE a cursor when you have finished it to prevent using too many server resources.

An alternative to a WHILE loop is to use a program marker and the GOTO statement.

System Procedures

The MASTER database contains several system procedures which are created when SQL Server is installed. These procedures have 'sp_' as a prefix to their name and are used mainly for administration purposes. They can be accessed from any database as long as the user has access rights to the master database. The procedures can be copied into your own database and edited to suit your needs.

One example is the system procedure sp_depends which returns the  dependencies of a SQL Server table, view or procedure object. The procedure returns a result set indicating all the objects upon which the object depends and all those that depend on it.

EXECUTE sp_depends 'authors'

There are many system procedures affecting all aspects of the database and server configuration. For example, batch scripts may be created to add users to a database. See the Transact SQL Reference manual for details.

There are hundreds of system stored procedures described in the online help. Some more useful system procedures are described below:

sp_helpdb	Details of the databases defined on the server.
sp_helpdb pubs	Details of the pubs database.
sp_help authors	Provides details on any database object.
sp_helptext byroyalry	Provides the text of a stored procedure.
sp_depends authors	Details of all objects that debend on the specified object.
sp_changeowner	Change the owner of an object (usually to dbo).
sp_rename	Rename an object.

 

Extended Procedures

Extended Procedures are used to call programs residing on the server automatically from a stored procedure or a trigger run by the server.

The extended stored procedures are held in the MASTER database and may be used to interact with the server. The following example is used to log an event in the NT event log of the server without raising any errors on the client application:

declare @logmessage varchar(100)

set @logmessage = suser_sname() + ': Attempted to access the bingo system.'

exec master..xp_logevent 50001, @logmessage

The XP_CMDSHELL command will run an operating system command on the server:

EXECUTE master..xp_cmdshell 'dir e:\*.*'

This functionality is very dangerous in the wrong hands as files may be deleted on the server or worse havoc caused.

An example extended procedure might call a Visual Basic program that runs on the server whenever an order is entered into the database which reads the SQL database in order to enter data into a FoxPro system that is used for Order Processing. This functionality could also be performed from the original application but implementing at the server level allows for orders to be created in a variety of front end implementations and yet always perform the required transactions.

DLLs may be created on the server and called within SQL Server as an extended procedure after registering the procedure with the sp_addextendedproc function.

Extended Mail Procedures

SQL Server includes extended procedures that facilitate the integration with Microsoft Mail. This allows an update trigger, for example monitoring stock levels, to generate an electronic mail message whenever the stock level falls below the reorder level.

SQL Server can be configured to “Auto Start Mail Client” when the SQL Server Service is started or Mail can be run on the server before starting the SQL Server service. Alternatively the mail client may be started on the server with the following extended procedure:

EXECUTE master..xp_startmail 

The startmail extended procedure can accept username and password to start a particular mail session if the setup defaults are not acceptable.

Mail may be sent to a mail user as a simple mail message or with the attachments of a file or results from a SQL Query:

EXECUTE master..xp_sendmail 'stamati crook', 'Reorder Disks 20303 Please!'

The mail procedures require the full user name as parameters. The shortened mail name will create an error.

SQL Server can also read mail to form part of an integrated Mail - Database Information strategy. There are extended procedures to read mail and to process queries attached to mail messages and attach the results set into a mail message and so on.

Error Handling

Stored procedures return a zero value by default. The convention is to return a zero value if the stored procedure is successful and a non-zero value for a failure.

declare @returnvalue int

exec @returnvalue = stpgetauthors

if @returnvalue <> 0

begin

The RAISERROR command is used to create error messages from the server which are returned to the application. The command will return an error number and a message to the calling application error handle.

RAISERROR ({msg_id | msg_str}, severity, state

[, argument1 [, argument2]] )

[WITH LOG]

The severity is a number from 0 to 25 although only system administrators should use values above 18. The convention is as follows:

·                  10 is for information only

·                  11-16 is for errors that can be corrected by the user

·                  17 is where system resources are exceeded

·                  18 is a non fatal system error

Severity levels 17 and above should be notified to the system administrator. The state is a number from 0 to 127 that can be used as you like.

Additional arguments can be included in an error message to provide additional information for a specific instance of the error. The following example raises an error and includes details of the author identifier and the number or records retrieved in the error message. It also records the error in the NT Event log of the server.

if @@rowcount <> 1

begin

    raiserror (

        'stpgetauthordetail: %s :Incorrect (%i) number of records found !',

        16,1,@authorid, @@rowcount ) with log

    return (2)

end

The default error number for a user created error is 50000. All user created errors should have an error number greater than 50000.

Error messages may be added into the database catalogue of error messages with the sp_addmessage stored procedure which stores a message against an error number and severity:

sp_addmessage 52001,16,'%s : Incorrect Parameters !'

The error is called with the RAISERRROR command without the need to supply the message text each time. This also allows messages to be displayed in multiple languages or system alerts to be defined to notify the system administrator immediately a particular error occurs.

if @authorid = null

begin

   raiserror(52001,16,1,'stpgetauthordetail-authorid')

   return (-1)

end

The @@ERROR system variable can be used to control errors a little more closely. The following stored procedure updates the value of the ZIP field in the authors table. This field has a constraint and will only allow a five numeric value to be applied. The @@ERROR value is used to trap for an error and return an explanatory error message to the client.

CREATE PROCEDURE stpsetauthorzip

@authorid id,

@zip char(5)

AS

update authors

   set zip = @zip

   where au_id = @authorid

if @@error <> 0

begin

   raiserror( 'Invalid ZIP code: %s',16,1,@zip)

   return (2)

end

The above example will return two error messages to the client. The first error is generated by SQL Server to indicate a failure of the constraint and the second generated by the user.

The error can also be trapped for in a stored procedure that traps the result code returned from another stored procedure:

declare @return int

exec @return = stpsetauthorzip '267-41-2394','74722'

if @return <> 0

   print 'failue'

else

   print 'ok'

Transactions

Stored Procedures often implement a series of transactions that update the database. Occasionally one of these transactions may fail, perhaps because another user has locked the resource or because a database constraint is activated, and an error is generated within the stored procedure. The programmer may need to control the transactions within the stored procedure to ensure that all or none of the transactions are written to the database.

Transactions are controlled with three commands:

·                  BEGIN TRANSACTION starts a transaction and also allows for nested transactions.

·                  COMMIT TRANSACTION will write all of the current transactions to the database.

·                  ROLLBACK TRANSACTION will undo all of the changes to the database for the current transaction.

SQL Server will 'write ahead' any changes to the database allowing for another user to read uncommitted data if they use the NOLOCK option of a SELECT statement.

The programmer can check for errors after each database update and then rollback the transaction if required. The following stored procedure adds an order to the SALES table provided that there is enough stock for the particular title indicated in the STOCKLEVEL field of the STOCK table.

Errors are monitored after each database update using the @@ERROR global variable and the whole transaction rolled back if an error occurs. This prevents the stock values from being debited if the subsequent order record cannot be created (for example if the order number is not unique).

 

CREATE PROCEDURE stpaddorder

    @storeid char(4),

    @orderid varchar(20),

    @titleid tid,

    @quantity int,

    @orderdate datetime,

    @payterms varchar(20) = 'Standard'

AS

 

/* 

Author: Stamati Crook

Date:   6 October 2001

Name:   stpaddorder

Purpose:

Adds an order into the sales table after checking that there is sufficient stock.

*/

 

DECLARE @errortrap int

SET @errortrap = 0

 

 

IF NOT EXISTS (SELECT title_id FROM stock WHERE title_id = @titleid )

BEGIN

    RAISERROR ( 'No Stock Record',16, 1)

    RETURN (-100)

END

 

IF (SELECT stocklevel FROM stock WHERE title_id = @titleid ) < @quantity

BEGIN

    RAISERROR ( 'Not enough stock',16, 1)

    RETURN (-101)

END

 

BEGIN TRANSACTION

 

UPDATE stock

    SET stocklevel = stocklevel - @quantity

    WHERE title_id = @titleid

 

SET @errortrap = @@error

 

IF @errortrap = 0

BEGIN

    INSERT INTO sales

        ( stor_id, ord_num, ord_date, qty, payterms, title_id )

        VALUES

        (@storeid, @orderid, @orderdate, @quantity, @payterms, @titleid )

    SET @errortrap = @@error

END

 

IF @errortrap = 0

BEGIN

    COMMIT

END

ELSE

BEGIN

    RAISERROR ( 'Error updating sales or stock table',16, 1)

    ROLLBACK

    RETURN (-102)

END

 

RETURN 0

 

Distributed Transactions

Distributed transactions can be controlled by the programmer to allow transactions on different SQL Servers to be committed or rolled back:

DECLARE @result int

BEGIN DISTRIBUTED TRANSACTION

EXECUTE @result = stpaddorder '7066', '240','PC1035',1,'2001-05-23'

IF @result = 0

EXECUTE @result = remote.pubs.dbo.stpaddorder '7066', '240','PC1035',1,'2001-05-23'

IF @result = 0

   COMMIT DISTRIBUTED TRANSACTION

ELSE

   ROLLBACK DISTRIBUTED TRANSACTION

RETURN @result

This type of functionality is often created as a middle tier component on Microsoft Transaction Server which can also control distributed transactions using the same transaction coordinator that SQL Server uses.