CODEWITHSUNDEEP
sqlsql-servert-sql
David
David

Reputation: 1105

Do a select depending of result of a another select

I have simple query like this:

    SELECT RoleId FROM Security.UserRoles WHERE UserId = 8

It throws me values 1 and 2.

I want to add a conditional if that select contains number 2 do another select like this:

    SELECT COUNT(O.OrderId) AS TotalItems FROM Store.[Order] O

I try something like this:

if not do an else clause with another select, how can I achieve that? Regards

 IF(SELECT RoleId FROM Security.UserRoles WHERE UserId = 8 =2)

but my syntax is not correct

Upvotes: 1

Views: 69

Answers (3)

Kedar Limaye
Kedar Limaye

Reputation: 1041

Use case statement,close to if 

SELECT 
( Case When RoleId =2 then
(  SELECT COUNT(O.OrderId) AS TotalItems FROM Store.[Order] O) else 0 end ) count

FROM Security.UserRoles WHERE UserId = 8

Upvotes: 0

Nishant Gupta
Nishant Gupta

Reputation: 3656

Use ELSE (IF...ELSE) in your case like this:

IF (SELECT SUM(CASE WHEN RoleID = 2 THEN 1 ELSE 0 END) FROM UserRoles WHERE UserId = 8) > 0 
    SELECT COUNT(O.OrderId) AS TotalItems FROM Store.[Order] O
ELSE 
    Query2;

GENERAL SYNTAX:

IF Boolean_expression   
     { sql_statement | statement_block }   
[ ELSE   
     { sql_statement | statement_block } ]

Link to demo:

http://sqlfiddle.com/#!18/bf55c/3

Upvotes: 1

qxg
qxg

Reputation: 7036

IF(EXISTS(SELECT RoleId FROM Security.UserRoles WHERE UserId = 8 AND RoleId =2))

Upvotes: 0

Related Questions