UNION with IF statements
14,599
Solution 1
Select Properties.Name, Properties.Result, Properties.Description
From (
Select 'password check', 1 As value, 'ERROR' As Result, 'There is a user with a blank password' As Description
Union All Select 'password check', 0, 'COMPLETED', 'OK'
Union All Select 'context properties check', 0, 'ERROR', 'No context property has been entered'
Union All Select 'context properties check', 1, 'COMPLETED', 'OK'
) As Properties
Join (
Select 'password check' As name
, Case
When Exists( Select 1 From member_table Where password Is Null ) Then 1
Else 0
End As Value
Union All
Select 'context properties check'
, Case
When Exists( Select 1 From server_context_properties ) Then 1
Else 0
End
) As Results
On Results.Name = Properties.Name
And Results.Value = Properties.Value
Solution 2
You can try this:
DECLARE @passwordcheck INT, @contextcheck INT
SELECT @passwordcheck = COUNT(*)
FROM member_table
WHERE [password] IS NULL
SELECT @contextcheck = COUNT(*)
FROM server_context_properties
SELECT 'password check' as name,
CASE WHEN @passwordcheck > 0 THEN 'ERROR' ELSE 'COMPLETED' END as result,
CASE WHEN @passwordcheck > 0 THEN 'there is user(s) with blank password' ELSE 'OK' as description
UNION
SELECT 'context properties check' as name,
CASE WHEN @contextcheck = 0 THEN 'ERROR' ELSE 'COMPLETED' END as result,
CASE WHEN @contextcheck = 0 THEN 'no context property has been entered' ELSE 'OK' END as description
Solution 3
maybe by doing this :
SELECT 'password check' as name, 'ERROR' as result ,'there is user(s) with blank password' as description
WHERE EXISTS ( select * from member_table WHERE password IS NULL )
UNION
SELECT 'password check' as name, 'ERROR' as result ,'there is user(s) with blank password' as description
WHERE NOT EXISTS ( select * from member_table WHERE password IS NULL )
UNION
SELECT 'context properties check' as name, 'ERROR' as result ,'no context property has been entered' as description
WHERE NOT EXISTS (select * from server_context_properties)
UNION
SELECT 'context properties check' as name, 'COMPLETED' as result, 'OK' as description
WHERE EXISTS (select * from server_context_properties)
Author by
Joe
Updated on June 04, 2022Comments
-
Joe almost 2 years
I'm writing a query that does various checks on database tables and returns a summary of the result(in a single result set)
IF ( select COUNT(*) from member_table WHERE password IS NULL ) > 0 SELECT 'password check' as name, 'ERROR' as result ,'there is user(s) with blank password' as description ELSE SELECT 'password check' as name, 'COMPLETED' as result, 'OK' as description UNION IF ( select COUNT(*) from server_context_properties ) = 0 SELECT 'context properties check' as name, 'ERROR' as result ,'no context property has been entered' as description ELSE SELECT 'context properties check' as name, 'COMPLETED' as result, 'OK' as description
the result table should look like this:
name result description password check COMPLETED OK contex properties check ERROR no context property has been entered
I know that the syntax is incorrect, but I can't think of any way to achieve this.