CODEWITHSUNDEEP
sqlsql-server
Lieven Cardoen
Lieven Cardoen

Reputation: 25969

Conditional operator in Transact-sql

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

Upvotes: 5

Views: 6142

Answers (3)

sphereinabox
sphereinabox

Reputation: 864

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

Upvotes: 4

Jeff Ferland
Jeff Ferland

Reputation: 18312

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

Give that a whirl

Upvotes: 2

Paul Creasey
Paul Creasey

Reputation: 28864

USE NULLIF()

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

Upvotes: 7

Related Questions