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:
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.
Featured Online Courses:
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Working with Python on Windows and SQL Server Databases
- Entity Framework: Getting Started – Complete Beginners Guide
- SQL Server 2019: What’s New – New and Enhanced Features
- Introduction to Azure Database for MySQL
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- How to Import and Export Data in SQL Server Databases
Read Also:
- What can you do using SQL Server?
- Benefits of Primary Keys in Database Tables
- How to Rebuild All Indexes Online for a SQL Server Database
- How to Write a “Hello World” App in Visual C++
- How to Write a “Hello World” App in C#
- Main Data Structures in Python
- What is SQL Server Management Studio?
- Difference Between SQL and SQL Server
- What are T-SQL Snippets in SSMS?
- Software Review: SQLNetHub’s Snippets Generator
Reference: {essentialDevTips.com} (http://www.essentialdevtips.com/)
© essentialDevTips.com
Rate this article:
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.