Articles, Videos and Courses on: SQL Server, .NET, Programming, C#, Python, SQL, Java and more! - EssentialDevTips.com

What are SQL Server Stored Procedures?

by

Hi friends! 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 essentialDevTips.com

 

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.

 

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

 

Learn More about SQL Server Stored Procedures

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

 

Learn more about SQL Server – Enroll to the course!

Enroll to our 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!

In this beginner-level course, you will learn how to install SQL Server Database on both Windows and Linux, and how you can start performing basic tasks in SQL Server, using its free client tools SQL Server Management Studio, and Azure Data Studio.

 

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

Learn More

 

 

Featured Online Courses:

 

Read Also:

 

Reference: {essentialDevTips.com} (http://www.essentialdevtips.com/)

© essentialDevTips.com

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

Loading...

Post Views: 200