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
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!
Learn More about SQL Server Stored Procedures
You can learn more, by checking out the official Microsoft documentation.
Featured Online Courses:
- SQL Server Fundamentals – SQL Database for Beginners
- A Guide on How to Start and Monetize a Successful Blog
- Introduction to Azure Database for MySQL
- Working with Python on Windows and SQL Server Databases
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to SQL Server Machine Learning Services
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- Benefits of Using Stored Procedures in Databases
- How to Get Started with SQL Server
- What is SQL Server Management Studio?
- What are T-SQL Snippets in SSMS?
- How do I Create a Formula in a Microsoft Excel Spreadsheet?
- What can you do using SQL Server?
- Difference Between SQL and SQL Server
- Differences Between Batch and Streaming Data
- How to Check SQL Server Version
- How to Create Databases in SQL Server
- How to Create and Alter Tables in SQL Server Databases
- How to Create Database Views in SQL Server and what are their Benefits
- All our articles on databases.
Featured Database Software Tool
Create and modify T-SQL code snippets for use in SQL Server Management Studio, fast, easy and efficiently.Learn More
Rate this article:
Reference: TechHowTos.com (https://www.techhowtos.com)