Stored Procedure

A stored procedure is a collection of T-SQL statements. Let us suppose you are running a query number of times. Every time you have to write in the 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 parameters:

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 without the 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 an 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 of the user does not supply any data to the variables in the procedure, using the default values, the 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 do 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 an output parameter.