What are SQL Server Stored Procedures?

Share this article!

In this article, you will learn what are SQL Server stored procedures, how you can define them, as well as how you can execute them.

 

About SQL Server Stored Procedures

A SQL Server stored procedure, is a batch of T-SQL statements, stored as a logical unit (programmability object) that has a name and can have input and output parameters.

When you call the stored procedure, you enter the values for the input parameters (if any), the stored procedure is then executed on the SQL Server database engine, and finally it returns its results (if any).

The infographic below, summarizes how you can use SQL Server stored procedures:

What are SQL Server Stored Procedures - Article on TechHowTos

 

How to Define a SQL Server Stored Procedure

Let’s see some T-SQL examples of defining stored procedures in SQL Server.

 

Defining a SQL Server Stored Procedure without Input Parameters

With the below T-SQL script, I’m creating a stored procedure named “uspNoParameters” in database “SampleDB1” that queries a table named “tblTest1” in order to return all of its records.

USE SampleDB1;
GO

CREATE PROCEDURE uspNoParameters
AS
    SELECT *
    FROM   dbo.tblTest1;
GO

As you can see from the above example, with the “CREATE PROCEDURE” T-SQL statement, followed by the stored procedure’s name and the ‘AS” keyword, I can then specify the query, or sets of queries to be be executed when the stored procedure is called for execution.

 

Defining a SQL Server Stored Procedure with Input Parameters

With the below T-SQL script, I’m creating a stored procedure named “uspParameters” in database “SampleDB1” that takes as input parameter an integer value (using the parameter @id) and based on this parameter, it queries a table named “tblTest1” in order to return the record that has as an id, the value passed as an input parameter to the stored procedure.

USE SampleDB1;
GO

CREATE PROCEDURE uspParameters
    @id INT
AS
    SELECT *
    FROM   dbo.tblTest1
    WHERE  id = @id;
GO

As you can see in the above example, right after specifying the stored procedure’s name and before the “AS” keyword, I’m specifying the input parameter’s name and type (@id int).

At that point, you can specify more than one input parameters if you need to do so.

Next, in the main part of the stored procedure, in the SELECT T-SQL statement, I’m using in the WHERE condition the value of the input parameter @id.

 

Read also: Benefits of Using Stored Procedures in Databases

 

How to Execute a SQL Server Stored Procedure

Now, based on the above two stored procedure definitions, let’s see the two corresponding examples of executing these stored procedures.

 

Executing a Stored Procedure without Input Parameters

In the below T-SQL statement, you can see how we can execute the stored procedure “uspNoParameters”, that is the stored procedure that takes no input parameters.

As you can see, we just make sure that we are in the correct database context, and we execute the stored procedure by specifying its name right after the EXEC statement.

 

USE SampleDB1;
GO

EXEC dbo.uspNoParameters;
GO

 

Executing a Stored Procedure with Input Parameters

In the below T-SQL statement, you can see how we can execute the stored procedure “uspParameters”, that is the stored procedure that takes an input parameter, that is @id.

As you can see, again, we make sure that we are in the correct database context, and we execute the stored procedure by specifying its name right after the EXEC statement, along with specifying the value for its input parameter (i.e. @id = 1).

USE SampleDB1;
GO

EXEC dbo.uspParameters @id = 1;
GO

 


Enroll to my online course on Udemy, titled “SQL Server Fundamentals – SQL Database for Beginners” and get started with SQL Server on both Windows and Linux in no time!

SQL Server Fundamentals - SQL Database for Beginners (Online Course)
(Lifetime Access, Downloadable Resources, Certificate of Completion and more!)

Enroll Now!


 

Learn More about SQL Server Stored Procedures

You can learn more, by checking out the official Microsoft documentation.

 

Featured Online Courses:

 

Read Also:

 

Featured Database Software Tool

SQLNetHub's Snippets Generator

 

SQLNetHub’s Snippets Generator 

Create and modify T-SQL code snippets for use in SQL Server Management Studio, fast, easy and efficiently.

Learn More

 

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)

Loading...

Reference: TechHowTos.com (https://www.techhowtos.com)

© TechHowTos.com