SQL Server Stored Procedure Examples

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.

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.