Stored Procedure

Stored Procedure

The syntax of the stored procedure:

CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH  [ ,...n ] ]
[ FOR REPLICATION ] 
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]

 ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

For more on syntax see here

Stored procedures are similar to procedures in other programming languages in that they can:
-Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
-Contain programming statements that perform operations in the database, including calling other procedures.
-Return a status value to a calling procedure or batch to indicate success or failure (and the reason for failure).

Example 1 of stored procedure

Below is a simple stored procedure

Create Procedure dbo.my_first_sp
As
select * from Employees
Go 

After writing the above text select it and press F5 in SQL Server. This stored procedure will be added under stored procedures(I am currently working with Northwind Database. Under Northwind Database, expand Programmability and expand stored procedures, you will see this new stored procedure my_first_sp).

In order to run it. Simply type

my_first_sp

Select it and press F5 or type

Exec my_first_sp

Select it and press F5.

Example 2 of stored procedure

Below is a simple stored procedure to show the employees by title of courtesy (Mr., Mrs., Dr. etc)

Create Procedure dbo.empbytitleofcourtesy @toc nvarchar(5)
As
select * from Employees
where TitleOfCourtesy = @toc
Go

Once you create this stored procedure, run it (select it and press F5). This stored procedure will be added under stored procedures(I am currently working with Northwind Database. Under Northwind Database, expand Programmability and expand stored procedures, you will see this new stored procedure empbytitleofcourtesy).

To run this stored procedure type:

Exec dbo.empbytitleofcourtesy 'Dr.'

And press F5.

We are passing one parameter ‘Dr.’(We want to see all employees who are doctors) to the stored procedure.

Example 3 of stored procedure

A simple stored procedure with 2 parameters

Create Procedure dbo.sp_two_parameters @fn varchar(20), @ln varchar (20)
As
select * from Employees
where FirstName = @fn and LastName = @ln
Go 

Call the above stroed procedure as 

sp_two_parameters 'Robert', 'King'

Example 4 of stored procedure

A simple stored procedure to insert rows into a table:

Create Procedure dbo.sp_insert @empid int, @nm varchar(20), @mng varchar(20)
As
Insert into employee values (@empid, @nm, @mng)
Go

Call the above stroed procedure as

sp_insert 4, 'ddd', 3

Example 5 of stored procedure

Example of stored procedure returning value

Create procedure sp_oparam @eid int, @nm varchar(20) output
As
Begin
Select @nm=Name 
from employee
where Emp_id = @eid
End
Go

DECLARE @Thename varchar(20)
Exec sp_oparam 3, @nm=@Thename output 
select Thename = @Thename
Go

Example 6 of stored procedure

Example of stored procedure returning 2 values

Create procedure sp_oparam @eid int, @nm varchar(20) output, @mngr int output
As
Begin
Select @nm=Name, @mngr=Manager 
from employee
where Emp_id = @eid
End
Go

DECLARE @Thename varchar(20), @Themanager int
Exec sp_oparam 2, @nm=@Thename output,  @mngr=@Themanager output 
select Thename = @Thename
select Themanager = @Themanager
Go

Example 7 of stored procedure

Another way to get data back from a stored procedure is by using Return. It only returns a single numeric value. Mostly this is used to return a status result or error code.

Alter procedure sp_ret @Mid int
As
Begin
 DECLARE @RetVal INT
 Select @RetVal=Emp_id from employee
 Where Manager = @Mid
if @RetVal = 4
 Begin
 Return 1
 End

End 

DECLARE @ReturnValue int

Exec @ReturnValue = sp_ret 3

select ReturnValue=@ReturnValue

Altering Stored Procedure
You alter stored procedure with ALTER PROCEDURE command. Advantage of using ALTER PROCEDURE to change a stored procedure is that it preserves access permissions, whereas CREATE PROCEDURE doesn't. A key difference between them is that ALTER PROCEDURE requires the use of the same encryption and recompile options as the original CREATE PROCEDURE statement.

Example 8 of stored procedure

Below is a simple example of stored procedure

CREATE procedure [dbo].[sp_willbechanged] @eid int 
As
Begin
Select Name, Manager 
from employee
where Emp_id = @eid
End 

Call the above stored procedure for example like below:
sp_willbechanged 2

Now if you want to modify the stored procedure and show only the Name and NOT Manager, you will do as below:

Alter procedure [dbo].[sp_willbechanged] @eid int 
As
Begin
Select Name 
from employee
where Emp_id = @eid
End

You will still continue to call this as you called the above one.

Example 9 of stored procedure

Below is an example of how to call one stored procedure from another stored procedure.

CREATE procedure [dbo].[sp_oparam1] @eid int, @nm varchar(20) output
As
Begin
 Select @nm=Name
 from employee
 where Emp_id = @eid
End
GO

Alter procedure [dbo].[sp_calloparam1] @eid1 int
As
Begin
 DECLARE @Thename varchar(20)
 Exec sp_oparam1 @eid1, @nm=@Thename output
If @Thename = 'aaa'
 Begin
  Return 5
 End 
End 
Go

Call sp_calloparam1 like below:

DECLARE @ReturnValue INT
EXEC @ReturnValue = sp_calloparam1 3
SELECT ReturnValue=@ReturnValue

How to list the source code of stored procedure?

Use sp_helptext to list the source code of the stored procedure.

How to find the creation and modification date of stored procedure?

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P'
AND name = 'sp_willbechanged'

Result:

Name   Create_date   Modify_date
sp_willbechanged 2011-06-13 09:12:17.747 2011-06-13 09:15:27.123