These simple SQL Server Stored procedure examples are powerful tools that can simplify your database development work. For example when dealing with complex business logic embedded into the SQL code. A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.
Table of Contents
1. Structure of a Basic SQL Server Stored Procedure
Let’s start with the basic structure of an MS SQL stored procedure. The CREATE PROCEDURE statement is used to create a stored procedure. It’s followed by the procedure name and, optionally, parameters that you want to pass to the procedure. Below is a simple example to create a generic procedure called MyProcedure.
In the example, the stored procedure named MyProcedure simply selects all records from the SQL table MyTable. The BEGIN and END keywords enclose the SQL statements.
CREATE PROCEDURE MyProcedure AS BEGIN SELECT * FROM MyTable END GO
2. Working with Parameters in Stored Procedures
Parameters can be used to pass values to the stored procedure. Now let’s modify MyProcedure to accept a unique parameter. As you can see, the @MyParam parameter is of type INT. It is used in the WHERE clause to filter records from MyTable.
CREATE PROCEDURE MyProcedure @MyParam INT AS BEGIN SELECT * FROM MyTable WHERE MyColumn = @MyParam END GO
3. Handling Errors in SQL Stored Procedures
Error handling is a vital aspect of writing robust T-SQL code. Fortunately, SQL Server provides TRY CATCH constructs for this purpose. Here, the TRY block contains the SQL code that might cause an exception.
Technically, it means that if an error occurs, the control is passed to the CATCH block where the error can be handled.
CREATE PROCEDURE MyProcedure @MyParam INT AS BEGIN TRY SELECT * FROM MyTable WHERE MyColumn = @MyParam END TRY BEGIN CATCH SELECT ERROR_MESSAGE() AS ErrorMessage; END CATCH GO
4. Use Conditional Logic in a SQL Procedure
SQL server stored procedure examples often use IF ELSE statements for conditional logic. This can greatly increase efficiency and maintainability.
Indeed, the IF-ELSE statement checks whether @MyParam is NULL. If it is not, a filtered select statement is executed. If it is NULL, all records are returned. Otherwise, the results would not return anything, another option is to set a default value for the parameter.
CREATE PROCEDURE MyProcedure @MyParam INT AS BEGIN IF @MyParam IS NOT NULL BEGIN SELECT * FROM MyTable WHERE MyColumn = @MyParam END ELSE BEGIN SELECT * FROM MyTable END END GO
5. T-SQL Procedure with a Loop
Looping constructs, like WHILE loops, can be used in SQL stored procedures to iterate over a set of records. This Microsoft documentation provides more insight on this.
So in this last example, a stored procedure named PrintIterations is created. When this procedure is executed, it will print a message for each iteration, 10 times in total.
The GO statement is a batch separator which signifies the end of the stored procedure creation and the beginning of any subsequent SQL commands. The command EXEC PrintIterations is a simple call to the stored procedure to execute its logic.
CREATE PROCEDURE PrintIterations AS BEGIN DECLARE @Counter INT = 1 WHILE @Counter <= 10 BEGIN PRINT 'This is loop iteration ' + CAST(@Counter AS NVARCHAR(2)) SET @Counter = @Counter + 1 END END GO -- To execute the stored procedure EXEC PrintIterations
Conclusion on using SQL Server Stored Procedures
Useful SQL stored procedures can greatly enhance the efficiency, readability and maintenance of your SQL server code. They allow encapsulation of complex business logic into reusable and modular programs. From handling errors, using conditional logic, to looping constructs, basic stored procedures in SQL server offer a range of possibilities. Consider using them in your next SQL server project for cleaner and more efficient code.