CODEWITHSUNDEEP
sqlsql-server
rohitnair
rohitnair

Reputation: 45

SQL Server - Set a bit variable using SELECT CASE WHEN, but default it to 0 if the SELECT returns no records

I'm using the following statements currently. How can I accomplish this logic with just one statement?

DECLARE @DisableCSDSync BIT

SET @DisableCSDSync = (SELECT 
                           CASE WHEN PropertyValue = 'true' 
                              THEN 1 
                              ELSE 0 
                           END 
                       FROM MyTable WITH (NOLOCK) 
                       WHERE Property = 'DisableSync' AND ParentId = 61040)

IF @DisableCSDSync IS NULL 
    SET @DisableCSDSync = 0

Upvotes: 0

Views: 4219

Answers (4)

Kris Khairallah
Kris Khairallah

Reputation: 1582

DECLARE @DisableCSDSync BIT

Set @DisableCSDSync = isnull( (SELECT Max( CASE WHEN PropertyValue = 'true' THEN 1 ELSE 0 END ) FROM MyTable WITH (NOLOCK) WHERE Property = 'DisableSync' AND ParentId = 61040 ) ,0)

Isnull will grant a default value 0 when you dont receive any record. The max will make sure you only receive 1 record. Multiple record will give you an error !

Upvotes: 0

Chintan Udeshi
Chintan Udeshi

Reputation: 362

This query will give you expected output even if select returns no records

DECLARE @DisableCSDSync BIT

SET @DisableCSDSync = ISNULL((SELECT CASE
                                       WHEN PropertyValue = 'true' THEN 1
                                       ELSE 0
                                     END
                              FROM   MyTable WITH (NOLOCK)
                              WHERE  Property = 'DisableSync'
                                     AND ParentId = 61040), 0)

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453067

You could also use the following

DECLARE @DisableCSDSync BIT = ISNULL(TRY_CAST((
                   SELECT PropertyValue
                   FROM   MyTable WITH (NOLOCK)
                   WHERE  Property = 'DisableSync'
                          AND ParentId = 61040) AS BIT), 0);

It uses the same ISNULL approach as one of the other answers to deal with the zero row case but also uses the same ISNULL (in conjunction with TRY_CAST .. AS BIT) to deal with the ELSE branch of the CASE.

The semantics are not exactly the same in that if PropertyValue is 1 it will be treated as true with the above approach but likely they are close enough.

Upvotes: 0

Gurwinder Singh
Gurwinder Singh

Reputation: 39467

You can set the default value when declaring the variable, so that the variable will have the default value in case there is no row fetched by your select query. Also, set the value in the select statement to prevent value from going to null if there are no rows to be fetched like this:

DECLARE @DisableCSDSync BIT = 0

SELECT @DisableCSDSync = CASE WHEN PropertyValue = 'true' THEN 1 ELSE 0 END FROM MyTable WITH (NOLOCK) WHERE Property = 'DisableSync' AND ParentId = 61040

Upvotes: 1

Related Questions