SQL: Creating a Stored Procedure Wrapper to Bypass ANSI NULLS and ANSI WARNINGS Error

SQL: Creating a Stored Procedure Wrapper to Bypass ANSI NULLS and ANSI WARNINGS Error

To bypass the ANSI Nulls and ANSI Warnings error upon execution of a query you can build a “wrapper” procedure to set the ANSI Nulls and WARNINGS ahead of calling your main procedure or query.

Below is an illustration of a simple wrapper that calls the main procedure.

Create the wrapper as illustrated below.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure proc_wrapper

(
@variable1 nvarchar(max),
@variable2 nvarchar(max)
)

AS
set ANSI_warnings on;
set ANSI_NULLS on;

EXEC proc_main_procedure @variable1, @variable2;

Below is a snippet of the main procedure that fails with errors if executed on its on.
It pulls data from two different DB’s off two different servers that use different schema and data types.
Calling it through the “wrapper” allows it to run properly.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure proc_main_procedure

@variable1 nvarchar(max), @variable2  nvarchar(max)

AS

Select . . .