Creating a simple stored procedure

We will create a simple stored procedure that joins two tables and returns the result set as shown in the following example.

CREATE PROCEDURE GetProductDesc AS BEGIN SET NOCOUNT ON  SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM Product PINNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID  END

We can use ‘EXEC ProcedureName’ to execute stored procedures.

Creating a stored procedure with parameters

Let us create a SQL Server stored procedure that accepts the input parameters and processes the records based on the input parameter.

Following is the example of a stored procedure that accepts the parameter.

CREATE PROCEDURE GetProductDesc_withparameters (@PID INT) AS BEGIN SET NOCOUNT ON  SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM Product PINNER JOIN ProductDescription PD ON P.ProductID=PD.ProductIDWHERE P.ProductID=@PID  END

EXEC GetProductDesc_withparameters 706

While executing the stored procedure we need to pass the input parameter.

Creating a stored procedure with default parameters values

Following is the example of a stored procedure with default parameter values.

CREATE PROCEDURE GetProductDesc_withDefaultparameters (@PID INT =706) AS BEGIN SET NOCOUNT ON  SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM Product PINNER JOIN ProductDescription PD ON P.ProductID=PD.ProductIDWHERE P.ProductID=@PID  END

When we execute the above procedure without passing the parameter value, the default value 706 will be used. But when executed passing the value, the default value will be ignored and the passed value will be considered as a parameter.

Creating a stored procedure with an output parameter

Below is the example of a stored procedure with an output parameter. The following example retrieves the EmpID which is an auto identity column when a new employee is inserted.

CREATE TABLE Employee (EmpID int identity(1,1),EmpName varchar(500))

CREATE PROCEDURE ins_NewEmp_with_outputparamaters (@Ename varchar(50), @EId int output) AS BEGIN SET NOCOUNT ON  INSERT INTO Employee (EmpName) VALUES (@Ename)  SELECT @EId= SCOPE_IDENTITY()  END

Executing the stored procedures with output parameters is bit different. We must declare the variable to store the value returned by the output parameter.

declare @EmpID INT  EXEC ins_NewEmp_with_outputparamaters 'Andrew’, @EmpID OUTPUT  SELECT @EmpID

Creating an encrypted stored procedure

We can hide the source code in the stored procedure by creating the procedure with the “ENCRYPTION” option.

Following is the example of an encrypted stored procedure.

CREATE PROCEDURE GetEmployees WITH ENCRYPTION AS BEGIN SET NOCOUNT ON  SELECT EmpID,EmpName from Employee END

When we try to view the code of the SQL Server stored procedure using sp_helptext, it returns “The text for object ‘GetEmployees’ is encrypted.”

Creating a temporary procedure

Like the temporary table, we can create temporary procedures as well. There are two types of temporary procedures, one is a local temporary stored procedure and another one is a global temporary procedure.

These procedures are created in the tempdb database.

Local temporary SQL Server stored procedures: These are created with # as prefix and can be accessed only in the session where it created. This procedure is automatically dropped when the connection is closed.

Following is the example of creating a local temporary procedure.

CREATE PROCEDURE #Temp AS BEGIN PRINT 'Local temp procedure’ END

Global temporary SQL Server stored procedure: These procedures are created with ## as prefix and can be accessed on the other sessions as well. This procedure is automatically dropped when the connection which is used to create the procedure is closed.

Below is the example of creating a global temporary procedure.

CREATE PROCEDURE ##TEMP AS BEGIN PRINT 'Global temp procedure’ END

Modifying the stored procedure

Use the ALTER PROCEDURE statement to modify the existing stored procedure. Following is the example of modifying the existing procedure.

ALTER PROCEDURE GetProductDesc AS BEGIN SET NOCOUNT ON  SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM Product PINNER JOIN ProductDescription PD ON P.ProductID=PD.ProductID  END

Renaming the stored procedure

To rename a stored procedure using T-SQL, use system stored procedure sp_rename. Following is the example that renames the procedure “GetProductDesc” to a new name “GetProductDesc_new”.

sp_rename 'GetProductDesc’,’GetProductDesc_new’