How to create a boolean in SQL that is checked every time I call it

Question

Let's say I have a table with 3 columns; people's salaries, how much they spend in a year, and their credit score.

Next, let's say I want to flag people with low salaries, who spend most of what they earn, and who also have bad credit scores. When doing some analytics on it, I may have to say something like

when(
    salary < 40000
     and
    spent > 0.75*salary
     and
    credit_score < 500
) then do_something

It's possible that I may have to reference those three conditions multiple times throughout a query. Can I just create a boolean that'll encompass all three of those?

For example

Declare @flag boolean  
@flag = case
    when(
        salary < 40000
         and
        spent > 0.75*salary
         and
        credit_score < 500
    ) then true
    else false

/* Then later on in the program */

    when(
    @flag
    ) then do_something

So the aim of the boolean is to shorten that three-condition statement into one variable and then the variable is referenced instead of the three statements. And every time it checks a row of data, it computes @flag and acts upon the result.

There is no such thing as a `boolean` in `SQL Server`. The most common way to indicate a bool in `SQL Server` is to use a `bit` (`0`/`1`). — Siyual, Jul 26 '16 at 14:47
user defined function? That would only apply if you wanted it to be a permanent part of the database. There's no temporary UDFs. — Damien_The_Unbeliever, Jul 26 '16 at 14:49
sql server has a datatype called bit. It can only be 0 or 1 and languages like c# or delphi can recognize it as boolean. In sql server you will have to check like this if @flag = 1 then — GuidoG, Jul 26 '16 at 14:50

Zohar Peled · Answer 1 · 2016-07-26T14:59:40.177

1

Sql server has no boolean data type available for users. You can use a Bit data type variable, that can hold 1, 0 or null values, like this:

Declare @flag bit
@flag = case
    when(
        salary < 40000
         and
        spent > 0.75*salary
         and
        credit_score < 600
    ) then 1
    else 0 end

/* Then later on in the program */

    when @flag = 1 then 
    -- do_something
    end

In case this is meant to be used for multiple rows, you can do something like this:

;WITH cte as
(
    SELECT  salary, 
            spent, 
            credit_score, 
            CAST(
                CASE WHEN  salary < 40000
                    AND spent > 0.75*salary
                    AND  credit_score < 600
                THEN 1
                ELSE 0 END
            As Bit) As SpentTooMuch
    FROM TableName
)

So now you have a bit column that's called SpentTooMuch that will hold the result of this calculation for every row in the table.

edited Jul 26 '16 at 14:59

answered Jul 26 '16 at 14:51

Zohar Peled

79,642
10
69
121

Of course not, but what in the question suggests that it should be a result of multiple rows? Am I missing something here? – Zohar Peled Jul 26 '16 at 14:54
because he says 'flag people' as in plural people? – Milney Jul 26 '16 at 14:55
@Milney: I see your point. edited my answer and upvoted yours. – Zohar Peled Jul 26 '16 at 15:01
Thanks for the response. And I'll clarify, when I said "flag people." I only intend on viewing individual people on a case-by-case basis, no one affects anyone else's results. – Greg G Jul 26 '16 at 15:03
well, in that case, a simple scalar variable of type bit will suffice. – Zohar Peled Jul 26 '16 at 15:05

Milney · Answer 2 · 2016-07-26T14:57:56.833

0

You should look into using a CTE, this will let you define this flag once, then you can use a join or apply to use this later on in your query.

Here are some useful references on CTEs;

https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

https://www.simple-talk.com/sql/t-sql-programming/sql-server-cte-basics/

You can do something like (obviously contrived example as I don't know your table structures and surrounding context but hopefully puts you on right path);

WITH FlagCTE AS (
SELECT 
    PersonId,
    CASE WHEN salary < 40000
          and spent > 0.75*salary
          and credit_score < 500 then 1 else 0 end AS BadFlag
FROM YourTable
)
SELECT 
    PersonId,
    CASE WHEN BadFlag = 1 AND SomethingElse THEN 10 ELSE 0 END AS OtherColumn
FROM YourTable tab
LEFT OUTER JOIN FlagCTE flag
    on tab.PersonId = flag.PersonId

edited Jul 26 '16 at 14:57

answered Jul 26 '16 at 14:49

Milney

6,253
2
19
33

not my downvote, i like the idea but why do you join yourtable with yourtable? – A ツ Jul 26 '16 at 14:58
Thank you for the reply. At a few points in my query, I do use CTEs when I need to update some Lag/Lead values since they are window functions. My only concern is the size of my table might make staging new information and joining take a while, but I'll keep this in mind. – Greg G Jul 26 '16 at 15:06
Actually using functions is more likely to give you a bad plan than joins if you are concerned about performance... Please read up on this. I will try add a link later but am too busy currently – Milney Jul 26 '16 at 15:12

Marko Juvančič · Answer 3 · 2016-07-26T15:19:09.867

0

Create a function and call it in your query.

ALTER FUNCTION [dbo].[GetMyFlag]
(
   @Salary decimal(18,2),
   @Spent decimal(18,2),
   @CreditScore int
)
RETURNS bit
AS
BEGIN
    IF @Salary < 40000 AND @Spent > 0.75*@Salary AND @CreditScore < 600
       RETURN 1

    RETURN 0
END

You can call it like this

 DELETE FROM [dbo].[MyTable] WHERE [dbo].GetMyFlag (salary, spent, credit_socre) = 1

edited Jul 26 '16 at 15:19

answered Jul 26 '16 at 14:53

Marko Juvančič

5,792
1
25
41

Thank you for the reply. How would I call this function? If I wanted to drop the records of those who meet the three conditions, would I say DELETE FROM [dbo].[MyTable] WHERE [dbo].[GetMyFlag] = 1? – Greg G Jul 26 '16 at 15:10

How to create a boolean in SQL that is checked every time I call it

3 Answers3