Stored procedures assist in achieving a consistent implementation of logic across applications. The SQL statements and logic needed to perform a commonly performed task can be designed, coded, and tested once in a stored procedure. Each application needing to perform that task can then simply execute the stored procedure.

Syntax to create Stored Procedure

CREATE PROCEDURE ProcedureName

AS

Body of the Procedure

Example
create procedure sp1
as
begin
      select * from Doctors
end

 

Now, once the procedure is created we need to execute procedure.

Syntax to execute procedure

EXECUTE ProcedureName

Or

EXEC ProcedureName

Example

EXECUTE sp1

 

Syntax for modifying procedure

ALTER PROCEDURE ProcedureName

AS

Body of Procedure

Example

ALTER PROCEDURE sp1
AS
BEGIN
      SELECT * FROM Doctors WHERE ID>4
END
Syntax for deleting procedure

DROP PROCEDURE ProcedureName

Example

DROP PROCEDURE sp1

Syntax for passing arguments to procedure

CREATE PROCEDURE ProcedureName

@ParameterName DataType

AS

Body of the Procedure

Example
CREATE PROCEDURE SP2
@IDNo INT
AS
BEGIN
      SELECT * FROM Doctors where ID=@IDNo
END
Executing parameterize procedure

EXECUTE sp25

 

Benefits of Stored Procedures
  •  Precompiled execution. SQL Server compiles each stored procedure once and   then reutilizes the execution plan. This results in tremendous performance   boosts when stored procedures are called repeatedly.
  •  Reduced client/server traffic.If network bandwidth is a concern in your   environment, you'll be happy to learn that stored procedures can reduce long   SQL queries to a single line that is transmitted over the wire.
  •  Efficient reuse of code and programming abstraction.Stored procedures can   be used by multiple users and client programs.
  •   Enhanced security controls.You can grant users permission to execute a              stored  procedure independently of underlying table permissions.
Difference between Function and Stored Procedure
  •    A function is a subprogram written to perform certain computations and            return a single value.
  •    Functions must return a value (using the RETURN keyword), but for stored           procedures this is not compulsory.
  •    Stored procedures can use RETURN keyword but without any value being           passed.
  •    Functions could be used in SELECT statements, provided they don’t do any         data manipulation. However, procedures cannot be included                                 in SELECT statements.
  •    A function can have only IN parameters, while stored procedures may                 have OUT or INOUT parameters.
  •   A stored procedure can return multiple values using the OUT parameter or        return no value at all. 
  1. It was really helpful to read this post on store procedure.

Leave Comment