Conditional operator in Transact-sql

Question

Is there a way to do this shorter, for instance using some sort of conditional operator in Transact-sql?

IF @ParentBinaryAssetStructureId = -1
BEGIN
    SET @ParentBinaryAssetStructureId = NULL
END

UPDATE  BinaryAssets.BinaryAssetStructures 
SET     ParentBinaryAssetStructureId = @ParentBinaryAssetStructureId
WHERE   BinaryAssetStructureId = @OriginalBinaryAssetStructureId

Answer 1

USE NULLIF()

UPDATE  BinaryAssets.BinaryAssetStructures 
SET     ParentBinaryAssetStructureId = NULLIF(@ParentBinaryAssetStructureId,-1)
WHERE   BinaryAssetStructureId = @OriginalBinaryAssetStructureId

Answer 2

UPDATE  BinaryAssets.BinaryAssetStructures 
SET     ParentBinaryAssetStructureId =
   CASE  ParentBinaryAssetStructureId  
     WHEN -1 THEN NULL
     ELSE ParentBinaryAssetStructureId
   END
WHERE   BinaryAssetStructureId = @OriginalBinaryAssetStructureId

Give that a whirl

Answer 3

The ternary (conditional) operator in c like languages:

x = doSomething ? 5 : 7

would be written like this in SQL:

SELECT @x = CASE WHEN @doSomething = 1 THEN 5 ELSE 0 END

There can be multiple cases (when clauses):

SELECT @x = CASE WHEN @doSomething = 1 THEN 5 WHEN @somethingElse = 1 THEN 20 ELSE 0 END