Conditionally Running SQL Based on Server

In data operations we often have to limit access to personally identifiable information (PII) in environments with fewer safe guards. So in production we may import personal identifiers like credit card numbers or government issued ids (social security numbers in the US). In a development environment we may want to treat that data with a mask on importing it. This can require two different import processes if written incorrectly, meaning that which is tested in dev or QA is not that which is promoted to production. Conditional logic can be based on server name, if that name is a consistent alias for the host managing the distributed transaction coordination. Here is a template.

USE <DATABASE>


IF (SELECT @@SERVERNAME) IN ('Server1\Instance', 'Server2')
BEGIN

    BEGIN TRY
        --insert your production code here

        --no changes past here
        COMMIT
    END TRY

    BEGIN CATCH
        ROLLBACK
        PRINT ERROR_MESSAGE()
    END CATCH
END


IF (SELECT @@SERVERNAME) NOT IN ('Server1\Instance', 'Server2')
BEGIN

    BEGIN TRY
        --insert your SPI masking code here

        --no changes past here
        COMMIT
    END TRY

    BEGIN CATCH
        ROLLBACK
        PRINT ERROR_MESSAGE()
    END CATCH
END