STORED PROCEDURES

Stored procedures are set of SQL statements that reside in a data dictionary in a complied form. They are executed by name and they can send and receive parameters. Stored Procedurs offer significant performance boost and reduce n/w traffic to a great extent.

In case of Stored procedure Parsing, Optimaziation functions are done at compile time. The first time the stored procedure executed a query execution plan is generated. This aolong with the complied form is stored in the data dictionary. This query plan is used by SQL server while executing the stored procedure.

Creating Stored Procedure

Stored procedures are created using the TSQL command Create Procedure.
A procedure definition consists of procedure name, a list of parameters and actual body of the procedure.

Syntax
create procedure
<procedure_name>
[ <parameter> <datatype> [=<default>]]
as Sql Statements

Stored procedures can be created onl within the current database. As the definition implies stored procedures can take parameters and default values can also be specified to these parameters.

Example
create procedure get_emp_info as
  select * from employee

Parameter Passing

Example
create procedure get_emp_name @emp_no integer
  as
  select emp_name from employee where empno = @emp_no

In the above example @emp_no is the parameter definition and this value is substituted during execution time. Stored procedures can include wild card characters also.

Example
create procedure get_emp_info @emp_name varchar(30) = null
as
if @emp_name is NULL
print "you must give the emp name"
else
select empno, ename, salary from employee
where ename like @emp_name

Execution of Stored Procedures
Stored procedures are executed using the EXECUTE statement. Parameters to stored procedures can be passed in 2 modes. One is parameter can be positionally, in which case parameter names are optional. The other option is paramter names can be specified in which case the order is not important.

Syntax
Execute <database>.<dbo>.<procedure> <parameter> = < value >...
with recompile

Example
sp_who

To view the stored procedure use sp_helptext system stored procedure. If the stored procedure saved in the ENCRYPTION mode then you cannot view the source of the stored procedure.
Example
sp_helptext emp_Get_info

Monitor Stored procedures
Stored procedures on execution return status values that help application detect completion or identify reasons for failure. This value can be stored in a variable when the stored procedure is called. The status can also be controlled using Return statement.
Follwoing are the list of Return Status Values
0 - Success
-1 Missing Object
-2 Datatype error
-3 Deadlock victim
-4 Permission error
-5 Syntax error

User generaed Return Values
reserves 0 and -1 through -99 to indicate different reasons of failure. Users can define their own status values outside the specified range.
Syntax
Return status_value

Example
create checkemployee @emp_no integer as
   if (select 1 from employee where empno = @emp_no) > 0
       Return 1
   Else
        Return 2

Example calling checkemployee stored procedure from another program

declare @retvalue int
declare @check_emp int
    Select @check_emp = 6002
    execute @retvalue = checkemployee( @check_emp )
    If @retvalue = 1
         print 'Employee is valid'
    Else
         print 'Employee is not valid'

Return Parameters

Parameters can be defined as return parameters using the keyword OUTPUT. The value of the return parameter can be passed back to the called, both caller and procedure must declare the parameter as output for the caller to receive the status value
Example
Create procedure add_numbers
     @number1 int, @number2 int, @result int output
as
      select @result = @number1 + @number2

Example of calling another stored procedure which has output.
declare @first_num int
declare @sec_num int
declare @added_value int
   Select @first_num = 35
   Select @sec_num = 45
   execute add_numbers @first_num, @sec_num, @result = @added_value output

Stored procedure rules
The create procedure statements cannot be combined with other SQL statments in a batch.
Create Procedure definition cannot include the following
Create view
Create default
Create rule
Create trigger
Create procedure

Other database objects can be created with a procedure
A procedure can call another stored procedure

control access to objects

Stored procedures can be used as a security mechanism. Users can be granted permission to execute a stored procedure even though they do not have any permission on the database objects referenced by that stored procedure.

System stored procedures
System provides certain procedures that perform many of the commonly needed user functions and administrative tasks. These procedures are called system procedures and are created when you install the server.
System procedures can be classified into two groups - those concerned with data definition and other concerned with system administration.

The system procedures for data definition allow the following operations on database and database objects.
binding and unbinding rules and defaults.
adding and dropping user_defined types and retrieving their definition.
retrieving information on databases and database objects.
The procedures in this category are
sp_addtype
sp_bindefault
sp_bindrule
sp_help

The system procedure for administration are typically used by SA, the DBO or object owners to perform administration tasks such as
adding and dropping logins changing the passwords and default databases.
performing stored management: adding devices and monitoring the space usages.
monitoring and tuning the server environment.
The procedures in this category are
sp_addlogin
sp_droplogin
sp_addgroup
sp_dropgroup
sp_adddevice
sp_dropdevice