CODEWITHSUNDEEP
sqlsql-server
Vũ Trần
Vũ Trần

Reputation: 71

Condition to filter out records if no rows are found

I have a simple code

SELECT @Setting =(CASE
                    WHEN @capName = 'role1'
                        AND .....?  
                        THEN 1
                    ......                    --More than 1 case
                    ELSE 0
                  END)
FROM table1 t
WHERE t.accountId = 'nameA'
AND t.capName IN ('role1','role2')

I want to have a condtion of column capName here so that when table1 is having no rows, the @Setting will return 1.

Upvotes: 0

Views: 72

Answers (3)

Muhammad Asad
Muhammad Asad

Reputation: 1782

As you mention you have multiple cases so please check out this code if this helps you. set @Setting to 1 by default if there is no row then by default you are getting 1 otherwise your cases does the trick.

declare @capName varchar(10) = 'role1',
@Setting int = 1
select @Setting = (CASE
                WHEN @capName = 'role1' 
                    THEN 1
                    WHEN @capName = 'role2' 
                    THEN 2
                    WHEN @capName = 'role3' 
                    THEN 3
                ELSE 0
              END)
FROM table1 t
WHERE t.accountId = 'nameA'
AND t.capName IN ('role1','role2')
SELECT @Setting as [setting]

Upvotes: 0

Serg
Serg

Reputation: 22811

Try

-- Default when no relevant rows exist
SET @Setting = 1; 
-- Try change it if any row exists
SELECT @Setting =(CASE
                    WHEN @capName = 'role1'
                        AND .....?  
                        THEN 1
                    ......                    --More than 1 case
                    ELSE 0
                  END)
FROM table1 t
WHERE t.accountId = 'nameA'
AND t.capName IN ('role1','role2')

Upvotes: 0

verhie
verhie

Reputation: 1318

Maybe COALESCE is what you're looking for:

SELECT @Setting = COALESCE((CASE
                WHEN @capName = 'role1'
                    AND .....?  
                    THEN 1
                ELSE 0
              END), 1)
FROM table1 t
WHERE t.accountId = 'nameA'
AND t.capName IN ('role1','role2')

Upvotes: 2

Related Questions