There is difference in execution of sp when deployed from dacpac and creating with ssms-CodePudding

In a SP I'm comparing a declared variable with empty string as below.

Declare @str1 nvarchar(max)

if(@str1 <> '')

   true

else

   false

I've deployed this sp through a database project and manually created a sp using ssms. During runtime, the sp created with dacpac is passing the condition and going to true and the one created with ssms is failing and going to false. Now I gave Script Procedure As => Alter to => new query window from object explorer for the sp created using dacpac and the same condition fails and the control goes to false.

Using this version:

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Enterprise 6.3 (Build 19041)

: )

CodePudding user response：

This has to do with the ANSI_NULLS setting (which should always be ON, FWIW, since OFF has been deprecated, but in one scenario you have it turned off, either explicitly or through SSMS Tools > Options).

Example db<>fiddle

Also I would probably change to this:

IF (@str1 > N'')

...which will work regardless of the ANSI_NULLS setting (example).

Or more explicitly (example):

IF (@str1 IS NOT NULL AND @str1 <> N'')

CodePudding user response：

Stored procedure meta-data includes 2 "sticky" SET options, QUOTED_IDENTIFIERS and ANSI_NULLS. The settings in effect the the proc is created/altered is used at execution time.

ANSI_NULLS is the culprit here. Consider:

SET ANSI_NULLS OFF;
GO
CREATE PROCEDURE dbo.Proc1
AS
DECLARE @str1 nvarchar(max);

if(@str1 <> '')
    PRINT 'true';
ELSE
    PRINT 'false';
GO


SET ANSI_NULLS ON;
GO
CREATE PROCEDURE dbo.Proc2
AS
DECLARE @str1 nvarchar(max);

if(@str1 <> '')
    PRINT 'true';
ELSE
    PRINT 'false';
GO

The above procs print different values when executed.

Note SSMS defaults to both options ON. The dacpac includes the setting for the proc which is used at deployment time. If you use a database project, both options are controlled by stored procedure properties. If you created the dacpac by reverse-engineering an existing database, the source object setting is stored.

I strongly recommend you consistently keep both options on in order to be compatible with indexed views, filtered indexes, etc.