STORED PROCEDURES

Stored procedures and its advantages

A stored procedure contains one or more SQL statements that you save so that you can reuse the code again. So if you have to write query again and again,create stored procedure and then execute it when ever required the stored procedure.You can call stored procedure from UI using ADO.Net which we will learn in the next section. You can have input and output parameters and Executes a series of SQL statements and return the query results.

Create and Execute stored procedures

Syntax for creating a procedure

CREATE PROCEDURE <Procedure_Name>

-- Add the parameters for the stored procedure here

<@Param1> <Datatype_For_Param1>,

<@Param2> <Datatype_For_Param2>

AS

BEGIN

 -- Write statements for procedure here

END

Syntax for executing a procedure

Execute Procedure_Name param1,param2

We can use Execute/Exec to execute a procedure followed by procedure. Provide parameters to procedures separated by commas. If the procedure does not have any parameter then use 

“Execute Procedure_Name” to execute a procedure. 

For example, if we have to retrieve name of the customer based on the phone number

SQL SELECT statement

SELECT CustomerName FROM Customer WHERE PhoneNumber = 9897969594;

Procedure is created to replace above query is as follows. Parameters specified where ever 

required.

CREATE PROCEDURE sp_GetName

@phoneNo varchar(10)

AS

begin

SELECT CustomerNameFROM Customer

WHERE PhoneNumber = @phoneNo

end

Explanation:

Procedure name is sp_GetName and input parameter to procedure is @phoneNo. All input 

parameters/local variables in SQL Server start with “@”. You can pass one to many parameters 

to procedures. Parameters have to be separated by commas

Run the stored procedure:

Stored procedure is executed by execute followed by procedure name and the required input 

parameters

Following is another sample which is used to update contact number and location of an 

employee by id

create proc sp_updateCustomer

(@contactNo bigint,

@location varchar(30),

@id int)

as

begin

update tblCustomer

set contactNo=@contactNo,Location=@location

where customerid=@id

end

Run the stored procedure:

In the above statements we have passed values for contact number, location and id when we execute the procedure. So the required details are updated. You can execute the same procedure with different values which enables you to reuse the procedure.

EXECUTE sp_GetName 9897675412

EXEC sp_updateCustomer 9898978790,'Delhi',5

Alter and drop stored procedure

We can alter a stored procedures by simply changing the required query and replacing create by alter keyword in a procedure.

For eg. Following is the procedure that is view customers

create proc sp_viewCustomer

select * from tblCustomer

Now there is a change in the requirement that we have to display the name and location of the 

customer. Then just do the following

alter proc sp_viewCustomer

select name,location from tblCustomer

Above procedure is modified in a way that it retrieves only name and location of the customer

Now if we have to remove a procedure permanently, we will use the following syntax

drop proc <Procedure_Name>

eg.

drop proc sp_viewCustomer

Output Parameters in Stored procedures

Output parameter is used in stored procedure to return a value to user. out keyword is used for output parameters. Value should be assigned to the parameter within the procedure.

Scenario: To insert customer to customer table and return the auto generated id.

create proc sp_insertCustomer

(@name varchar(30),

@contactNo bigint,

@location varchar(30),

@id int out)

insert into tblCustomer

values(@name,@contactNo,@location)

set @id=@@identity

Executing a procedure having output parameter

Declare @result int

Execute sp_insertCustomer 'Asha',9898989898,'Mumbai',@id= @result output

Print @result

Explanation:

set @id=@@identity - set keyword is used to do assignment in SQL. @@identity is a global variable which is used to retrieve the auto generated values. It should be used next to an insert statement.

Reference Links

 http://technet.microsoft.com/en-us/library/aa214299(v=sql.80).aspx

 http://msdn.microsoft.com/en-IN/library/ms345415.aspx

 http://msdn.microsoft.com/en-IN/library/ms188927.aspx