Stored Procedures - Chapter 8

Stored Procedures
A stored procedure is an executable database object that exists 
independently of a table. It can be called from a client or from another
procedure or trigger, parameters can be passed and returned, and error 
codes can be checked. 

Stored Procedure Advantages Stored Procedures provide many benefits
over executing large and complex SQL batches from a client
workstattion:

. Faster execution. Stored procedures, after their first execution,
become
  memory-resident and don't need to be reparsed, reoptimized, and
  recompiled.

. Reduced network traffic. Stored procedures can consist of hundreds of
  individual SQL statements, but can be executed with a single
  statement.  This allows you to reduce the size of the call from the
  client to the server.

. Modular programming. Procedures provide a method of breaking things
into
  smaller, more manageable pieces.

. Restricted, function-based access to tables. You can grant
permissions
  in such a way as to allow a user access to tables only through the
  stored procedure.

. Reduced operator error.  There is less information to pass.

. Enforced consistency.  If users are accessing tables only through
stored
  procedures, problems resulting from ad hoc data modifications are
  eliminated.

. Automated complex or sensitive transactions. By requiring all
interaction
  with certain tables to take place in a stored procedure, you can
  guarantee data integrity on those tables.

Running Stored Procedures
Stored procedures improves overall system performance because optimization 
and compilation by the SQL Server enhances procedure performance and storing 
procedures where they are to be used reduces network traffic. 
The first time a procedure is executed, the server must optimize and compile it.
That compiled optimization plan is stored in procedure cache and may be 
available when the procedure is run later. If so, the optimization and 
compilation steps are skipped.

Creating Stored Procedures
To write a stored procedure, you need to provide the procedure with a unique
name, and then write the sequence of SQL statements to be included in the
procedure.  It's good programming practice to end the stored procedure
with a retun statement.

create proc procedure_name
as
SQL statements
[return [status_value]]

create proc pub_titles
as
select t.title, p.pub_name
   from publishers p join titles t on p.pub_id = t.pub_id
return

To execute a stored pprocedure, simply invoke it by name.
Use the exec[ute] keyword if the call to the stored procedure isn't
the first statement in the batch.

Here's the syntax for the execution step:

[exec[ute]] proc_name

these two examples do the same thing.

pub_titles

exec pub_titles

To drop a stored procedure, use the drop proc statement:

drop proc proc_name


renaming Stored Procedures

Procedures can be renamed by using sp_rename. To modify a stored procedure,
drop the procedure and re-create it.

/* check if the procedure exists and drop it */
if exists (select * from sysobjects
   where name = "titles_for_a_pub"
   and type = "p" and uid = user_id())
drop proc titles_for_a_pub
go
create proc titles_for_a_pub
as...


Displaying Procedure Code in SQL/w

sp_helptext titles_for_a_pub

sp_help titles_for_a_pub

* sp_help reports parameters' names and datatypes for a stored procedure.

Procedures and Parameters
Stored Procedures can accept parameters to improve their usefulness
and flexibilty. The parameters are declared at the top of the procedure:

create proc procedure_name
	(parameter_name datatype [,...])
as
sql_statements
[return [status_value]]

[exec[ute]] procedure_name [expression] [,...]

here's an example of a stored procedure with a single parameter, @pub_name.

create proc titles_for_a_pub
	(@pub_name varchar (40))
as
select t.title from publishers p, titles t
	where p.pub_id = t.pub_id
	and pub_name like @pub_name
return

The procedure accepts a publisher name and looks up publishers based on that name.

titles_for_a_pub 'Algo%'


exec titles_for_a_pub 'New Moon Books'

Executing with Parameters
At execution time, parameters can be specified by position or by name.
If passed by name, parameters can be passed in any order.

Here's an example of a stored procedure that requires three
parameters:

create proc myproc
	(@val1 int, @val2 int, @val3 int) as
...
go

In this example, the parameters are passed by position. The execute statement
doesn't provide any parameter names, so the values are assigned to the parameters
in the order they were defined in the create statement (@val1, @val2, @val3):

/* parameters passed by position here */
exec myproc 10, 20, 15


/* parameter passed by name */
exec myproc @val2 = 20, @val1 = 10, @val3 = 15

Default Parameter Values
Stored procedure parameters can be assigned default values if no value is 
supplied during execution. You can improve your stored procedure code by
defining defaults for all parameters:

create proc procedure_name
	(@parameter_name datatype = default_value
	[,...])
as
SQL Statements
[return [status_value]]

/* check for a pub_name before executing query */
create proc titles_for-a_pub
	(@pub_name varchar (40) = null)
as
if @pub_name = null
	begin
		print "pass in the pub_name as a parameter"
		return
	end
select t.title from publishers p, titles t
	where p.pub_id = t.pub_id
	and pub_name like @pub_name + "%"
return


If you execute without providing a parameter value, you get the error
message you wrote:

/* without a parameter, you get the message */

titles_for_a_pub
go

pass in the pub_name as a parameter

/* with a parameter, you get the results */
titles_for_a_pub "Algo%"
go

titles
-----------------------------

Passing Parameters In and Out
Stored procedure parameters can be passed both in and out. The output keyword
identifies parameters that can be returned to the calling batch or procedure. 

create proc procedure_name
	[(@parm_name datatype = default_value [output]
	[,...])]
as
SQL Statements
[return [status_value]]


[exec[ute]] procedure_name
	[[@parm_name = ] expression [output][,...]

/* passing a parameter back to calling batch */
create proc ytd_sales
	(@title varchar (80) = null,
	 @ytd_sales int output)
as
if @title = null
	begin
		print "Syntax: ytd_sales title, @variable [output]"
		return
	end
select @ytd_sales = ytd_sales
	from titles
	where title = @title
return

In this example, the procedure accepts two parameters, @title and @ytd_sales.
The second parameter is keyed for output, meaning that its value at the end
of the procedure will be available for return to the calling process.

The execute statement needs to declare a variable to store the returned value
(@sales_figure). The datatype of the variable should match the datatype of the
output parameter. The execute statement itself needs to include the keyword 
output to complete the chain and permit the value of the parameter to be 
returned to the variable.

/* variable must be set up to accept output value
** in this example, total sales are returned by position */
declare @sales_figure int
exec ytd_sales 'But Is It User Friendly?', @sales_figure output
select 'Ytd sales of But Is It User Friendly? = ', @sales_figure
go


------------------------
ytd sales of But Is It User Friendly? = 8780
(1 row affected )


Inserting Data with Procedure Result Sets
SQL Server 6.5 allows you to use the results from a procedure as the basis
for an insert into a table.  While this adds some flexibility in writing 
application code, its most important application is the ability to execute 
a remote procedure on another SQL Server and use those results as the basis 
of an insertion.

Here's the syntax of the new insert statement:

insert tablename [(columnname [,...])]
execute [@statusvar =] procedure_name [parameter [output][,...]

Example,

create procedure ins_proc
as
select "1390", pub_name, city, state
from publishers
where pub_id = "1389"
return 0

Here's  the insert statement:

declare @status int
insert publishers (pub_id, pub_name, city, state)
execute @status = ins_proc

Procedure Limitations and Notes
A stored procedure can't create views, defaults, rules, triggers, or procedures,
nor issue the use statement.

Objects Referenced in Procedures
To display a list of objects referenced by stored procedure, use sp_depends:

exec sp_depends procedure_name

To display a list of stored procedures that reference a specific table or view,
use sp_depends this way:

exec sp_depends {table_name | view_name}


Optimizing Stored Procedures
The SQL Server optimizer generates a query plan for a stored procedure based
on the parameters passed in the first time it's executed. This query plan
is then run from cache for subsequent executions. To force a new query plan 
to be generated, use the with recompile option either at creation time or
execution time.

Example 

create proc advantage_range
	(@low money, @high money)
	with recompile
as
select * from titles
	where advance between @low and @high
return
go

/* if the procedure has not been created with the
** with recompile option, execute as follows to recompile */
exec advance_range $1000, $2000 with recompile

* An index has been added to table

Remote Stored Procedures
Remote stored procedures are procedures residing on other servers. Fully
qualify the stored procedure name with the server name to execute procedures
on other servers:

[exec[ute]] server_name.db_name.owner.proc_name

Stored Procedures Guidelines
.  Stored procedures should be solid because they're server-resident and called
   frequently.

. Check parameters for validity, and return an error if there's a problem.

. Ensure that the parameter datatypes match the column datatypes with which
  they're compared, to avoid datatypes mismatches.

. Check @@error after each SQL statement.

. Comment your code

. Always use a return statement.

. Develop a method for maintaining versions of stored procedure source code.


Stored Procedure Debugging Techniques

To get the syntax right on a stored procedure, write small parts of it as a 
batch first; then store the procedure after the whole operation starts working.

Get showplan output with recompile. The output from showplan and dbcc generated
by the query optimizer, which operates only when a procedure is recompiled.
To see the effects of different parameters on optimization, create the procedure
with recompile; then drop and re-create the procedure without recompile when
you go into production.

Last update February 4, 1998.