SA0191 : Procedure body is not enclosed in BEGIN...END block
Introduction
Section titled “Introduction”Enclosing the body of a stored procedure within a BEGIN..END block ensures clarity, consistency, and prevents confusion, especially in complex T-SQL code.
Description
Section titled “Description”It is a common practice in T-SQL code to enclose the body of a stored procedure within a BEGIN..END block. This practice ensures clarity and maintains consistency, especially as procedures become more complex.
For example:
-- Problematic procedure definitionCREATE PROCEDURE SampleProcedureASSELECT * FROM Employees;In the example above, the absence of a BEGIN..END block may lead to maintenance challenges. Although SQL Server does not require this structure, overlooking it can introduce issues when adding statements or handling complex logic.
-
Lacks clarity: For those maintaining the code, not using
BEGIN..ENDcan make it harder to discern the intended logic boundaries. -
Error-prone modifications: As additional logic is introduced, failing to use
BEGIN..ENDmay lead to syntax errors or unintended behavior.
How to fix
Section titled “How to fix”Enclose the procedure body explicitly within a BEGIN..END block to improve clarity and prevent potential issues in maintenance and expansion.
Follow these steps to address the issue:
1.Identify procedures that lack a BEGIN..END block. In SQL Server Management Studio (SSMS), you can locate such procedures by reviewing the SQL definitions or using code analysis tools.
2.Edit the procedure. Wrap the body of the procedure (the statements between AS and the end of the procedure) within a BEGIN..END block.
3.Apply the changes by saving or re-deploying the procedure to the database. Ensure there are no syntax errors and that the logic remains correctly defined within the block.
For example:
-- Corrected procedure definitionCREATE PROCEDURE SampleProcedureASBEGIN SELECT * FROM Employees;END;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”2 minutes per issue.
Categories
Section titled “Categories”Design Rules, Code Smells
Additional Information
Section titled “Additional Information”There is no additional info for this rule.
Example Test SQL
Section titled “Example Test SQL”CREATE PROCEDURE HumanResources.uspGetAllEmployeesASSET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, DepartmentFROM HumanResources.vEmployeeDepartment;Example Test SQL with Automatic Fix
Section titled “Example Test SQL with Automatic Fix”CREATE PROCEDURE HumanResources.uspGetAllEmployeesASBEGIN
SET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, DepartmentFROM HumanResources.vEmployeeDepartment;
ENDAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0191 : Procedure body is not enclosed in BEGIN…END block. | 1 | 0 |