CODEWITHSUNDEEP
sql-servert-sql
Robin
Robin

Reputation: 165

TSQL Select runs even within IF statement that does not execute

Final Edit; Got it working by updating

IF @@servername = 'Production'

to 

IF ServerProperty('ServerName')  = 'Production'

Thanks shawnt00 for stepping through this!

I have a peculiar issue where in test the database names are suffixed with test but in live they do not exist.

So when testing to release code to production I have to write an if statement that states if am on test, use the test database otherwise use the production database. As shown below.

However, the issue seems that even if I am on Test, the select statement for the production database still runs and throws an error due to the Database not existing on test, only DatabaseTest does.

On Test @@servername will be 'Test'. On prod, it will be 'Production'.

DECLARE @serverName VARCHAR(50)= @@servername;
DECLARE @WhoModified VARCHAR(30)= 'Me';
DECLARE @DateModified DATETIME= GETDATE();
DECLARE @StatusNameCancel VARCHAR(30)= 'Cancelled';
DECLARE @StatusCodeCancel INT= 0;


IF @@servername = 'Production'
BEGIN;
    SELECT @StatusCodeCancel = Value
    FROM DatabaseA.dbo.TableA
    WHERE ParameterName = @StatusNameCancel;

    UPDATE [DatabaseA].[dbo].[TableB]
      SET
          StatusCode = @StatusCodeCancel,
          WhoModified = @WhoModified,
          WhenModified = @DateModified
    WHERE ID IN(1, 2);
END;

IF @@servername = 'Test'
BEGIN

    SELECT @StatusCodeCancel = ParameterValue
    FROM DatabaseATest.dbo.TableA
    WHERE ParameterName = @StatusNameCancel;

    UPDATE [DatabaseATest].[dbo].[TableB]
      SET
          StatusCode = @StatusCodeCancel,
          WhoModified = @WhoModified,
          WhenModified = @DateModified
     WHERE ID IN(1, 2);
END;

Is there any workaround to this?

enter image description here

Upvotes: 0

Views: 410

Answers (1)

shawnt00
shawnt00

Reputation: 17915

I would double-check the server name. Make sure these values all look right:

SELECT
    ServerProperty('machinename') as [machinename], 
    ServerProperty('ServerName') as [ServerName],
    @@ServerName as [@@ServerName];

https://learn.microsoft.com/en-us/sql/t-sql/functions/servername-transact-sql

Upvotes: 1

Related Questions