Skip to content

SA0191 : Procedure body is not enclosed in BEGIN...END block

Enclosing the body of a stored procedure within a BEGIN..END block ensures clarity, consistency, and prevents confusion, especially in complex T-SQL code.

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 definition
CREATE PROCEDURE SampleProcedure
AS
SELECT * 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..END can make it harder to discern the intended logic boundaries.

  • Error-prone modifications: As additional logic is introduced, failing to use BEGIN..END may lead to syntax errors or unintended behavior.

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 definition
CREATE PROCEDURE SampleProcedure
AS
BEGIN
SELECT * FROM Employees;
END;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

2 minutes per issue.

Design Rules, Code Smells

There is no additional info for this rule.

CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
SET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment;
CREATE PROCEDURE HumanResources.uspGetAllEmployees
AS
BEGIN
SET NOCOUNT ON;
SELECT LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment;
END
  Message Line Column
1 SA0191 : Procedure body is not enclosed in BEGIN…END block. 1 0

Analysis Rules