How to compile a stored procedure without executing the actual procedure to see potential problems.

Problem: How to compile a stored procedure without executing the actual procedure to see potential problems.

Solution: NOEXEC

When deploying the procedures to a production database where you want to know the compilation errors without executing the procedure. For example there are validation errors which did not come up until the first time of the execution of a procedure, which would be hard to find.

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.

The execution of statements in SQL Server has two phases: compilation and execution. This setting is useful for having SQL Server validate the syntax and object names in Transact-SQL code when executing. It is also useful for debugging statements that would generally be part of a larger batch of statements.

The setting of SET NOEXEC is set at execute or run time and not at parse time.

Example:

In this article, I am going to show we can get a potential error of a procedure without executing it.

CREATE PROCEDURE [dbo].[getdatabyDate]

@InpudateDate DATETIME

AS

BEGIN

insert into dbo.TableA

select Col1,col2,col3 from dbo.TableB

END

drop table tableA

set showplan_text on

go

set noexec on

EXEC [dbo].[getdatabyDate]

set noexec off

go

set showplan_text off

go

Results:

Msg 208, Level 16, State 1, Procedure getdatabyDate, Line 8

Invalid object name 'dbo.TableA'.

Featured Script:

This below script checks for compile error without executing all the procedures that are currently present inside a database.

Set NOCOUNT   ON;

SELECT 'set showplan_text on' stmCompiledProcName

UNION ALL

SELECT 'go' stmCompiledProcName

UNION ALL

SELECT 'set noexec on'

UNION ALL

SELECT 'EXEC [' + SPECIFIC_SCHEMA + '].[' + SPECIFIC_NAME + ']' AS stmCompiledProcName

FROM information_schema.routines

WHERE routine_type = 'PROCEDURE'