Sql Server Stored Procedures

Creating a Simple Stored Procedure

This example shows how to create a stored procedure that joins two tables and returns the results.

CREATE PROCEDURE GetProductsDesc
AS
BEGIN
SET NOCOUNT ON
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Products P
INNER JOIN ProductDescriptions PD ON P.ProductID=PD.ProductID
END

To execute stored procedures, we can use 'EXEC ProcedureName'. Using the procedure GetProductsDesc, we get the following results.

Creating a Stored Procedure with Parameters

In this example, we will develop a stored procedure that accepts input parameters and processes records based on those input parameters.

In the following example, the parameter is accepted by a stored procedure.

CREATE PROCEDURE GetProductsDesc_withparameters
(@PID INT)
AS
BEGIN
SET NOCOUNT ON
SELECT P.ProductID,P.ProductName,PD.ProductDescription  FROM 
Products P
INNER JOIN ProductsDescription PD ON P.ProductID=PD.ProductID
WHERE P.ProductID=@PID
END

EXEC GetProductDesc_withparameters 706

The input parameter must be passed to the stored procedure while it is being executed. The result set can be seen in the image below.

Creating a Stored Procedure with Output Parameter

In the example below, there is an output parameter for a stored procedure. When an employee is inserted, the EmpID column is an auto-identification column.

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

Using output parameters to execute stored procedures is a bit different. In order to store the value returned by the output parameter, we need to declare a variable.

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

Creating a Temporary Procedure

Temporary procedures can also be created, just like temporary tables. Temporary procedures come in two types, a local temporary stored procedure and a global temporary stored procedure.

In the tempdb database, these procedures are created.

Local temporary Sql Server stored procedures: They are created with # as prefix and are accessible only in the session where they were created. Closing the connection automatically drops this procedure.

A local temporary procedure is shown in the following example.

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

Global temporary SQL Server stored procedure: In addition to being accessed on other sessions, these procedures are created with ## as prefix. Upon closing the connection used to create this procedure, the procedure is automatically dropped.

A global temporary procedure is shown in the following example.

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

Modifying the Stored Procedure

Modify an existing stored procedure with the ALTER PROCEDURE statement. The following example shows how this can be done.

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

Renaming the Stored Procedure

The system stored procedure sp_rename can be used to rename a stored procedure using T-SQL. Here is an example of renaming the procedure "GetProductDesc" to "GetProductDesc_new". 

sp_rename 'GetProductDesc','GetProductDesc_new'

Must Read SQL Clauses

Frequently Asked Questions

What are the reasons why stored procedures should not be used?

Especially with stored procedures, you are encouraging bad development practices, since you have to list out each field in a database table half a dozen or more times. A single column added to your database table is a huge pain. 

Can stored procedures be reused?

It is possible to execute stored procedures from multiple clients without having to write the code again.

Functions versus stored procedures: What's the difference?

It is optional in Stored Procedures for the function to return a value. Even procedures can return zero or n values.

How are stored procedures and user-defined functions different?

It is only possible to read select statements with the user-defined function, while DML statements are not allowed.

What are the advantages of stored procedures over queries?

After queries are submitted, they are compiled and executed. Unlike queries, stored procedures are compiled when submitted for the first time, & this compiled content is stored in something called procedure cache, so subsequent calls will not require compilation, just execution.  

Conclusion

Throughout this article, we examined different SQL Server stored procedures with examples. Please feel free to ask any questions below in the comment sect⁠⁠⁠⁠⁠i⁠⁠⁠⁠⁠⁠on.

Check out this link if you want to learn SQL.

Refer to our Guided Path on Coding Ninjas Studio to upskill yourself in MongoDB, Competitive Programming, Databases, System Design and many more!

Do upvote our blogs if you find them helpful and engaging!

Happy Learning!!