T-SQL 101: 124 Understanding Stored Procedures in SQL Server T-SQL

T-SQL 101: 124 Understanding Stored Procedures in SQL Server T-SQL

Stored procedures are prepackaged groups of T-SQL statements. They can also be written in other languages, but most of the time they’ll be written in T-SQL.

There are pros and cons with using stored procedures.

In the example shown, I use the EXEC statement to execute a stored procedure called SDU_Tools.ListAllDataTypesInUse.

Parameters

The procedure also takes a number of parameters. By using different parameters values, I can change the behaviour of the stored procedure.

It is also possible to have both input and output parameters. I’ll discuss those in a later post in this series.

Pros

There are a number of benefits that stored procedures provide.

  • I can execute the stored procedure without knowing how it works. This layer of abstraction is useful.
  • All I need to send across the network is the code to execute the procedure, rather than needing to send all the code that’s contained in the query or queries within the procedure.
  • The procedure is also a security boundary. I can create a procedure that takes actions that the user who is running it, cannot take.
  • The query execution plan for a procedure can easily be reused, removing the need for recompilation.

Cons

The most common issue with stored procedures in SQL Server is related to what is called parameter sniffing. This refers to a query execution plan being created for one set of parameters and then being reused for other parameters where it is not appropriate. I’ll discuss that in a later post in this series.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-03-04