Stored Procedures - SQL Server

A stored procedure is a collection of T-SQL statements. Let us suppose you are running a query number of time office. Every time you have to write in query and execute it. Instead of that, you can save/store the query in a stored procedure with a specific name and you can invoke the procedure by calling that name.

Using stored procedure user can do CREATE, UPDATE, INSERT and DELETE operations. 

Syntax:

CREATE PROCEDURE/PROC <PROCEDURE_NAME>

@parameter1,2,… DATATYPE

AS

BEGIN

T-SQL statements1

T-SQL statements2

T-SQL statements3

T-SQL statements4

Etc…

END

Example:

CREATE PROCEDURE [dbo].[sp_Empinfo]

AS

BEGIN

     SELECT * FROM tbl_Employee

END

ALTER:

Alter PROCEDURE PROCEDURE/PROC <PROCEDURE_NAME>

@parameter1,2,… DATATYPE

AS

BEGIN

T-SQL statements1

T-SQL statements2

T-SQL statements3

T-SQL statements4

Etc…

END

Drop

DROP PROCEDURE sp_Stateinfo

Parameters/Input paramters:

Parameters are used to get specific data from a procedure. In general we will pass values to where condition in the query.  

Example:

CREATE PROCEDURE [dbo].[sp_Empinfo]

@empno Int  ---- Parameter

AS

BEGIN

     SELECT * FROM tbl_Employee WHERE Empno = @empno

END

Output parameters

Output parameters are used to catch the procedure result into a variable. Output parameters are declared with out keyword.

CREATE PROCEDURE [dbo].[sp_Empinfo]

@empno Int,

@count INT Out/Output

AS

BEGIN

     SELECT @count = count(*) FROM tbl_Employee WHERE Empno = @empno

END

To catch the output, we need to create a variable with the same data type, which we declared in the procedure as output parameter.

DECLARE @cnt int

EXEC sp_Empinfo @cnt OUT

select @cnt

Then @cnt will catch the result from the sp_Empinfo Procedure.

Optional parameters:

Optional parameters will contain default values. In case user not  supply any data to the variables in the procedure, using  the default values, output will be generated.

Example: I want to write a procedure to return employees from employee table. If you pass EmpID related employee information should return. If you not pass any parameter, all employee information should return.

CREATE PROCEDURE ved_Opionalprarameters

@empno VARCHAR(50) = null

AS

BEGIN

SELECT * FROM tbl_Employee WHERE EmpNo = @empno OR @empno IS NULL

END

In the above example I declared @empno as output parameter.