My sproc code, simplified:

declare @x int
declare @y int
declare @Result bit

--Assign @x and @y by running some complex code.
--For testing we will say:
set @x = 1
set @y = 2

--Now I want to compare them, ideally this way would return true or
false:
select @Result = (@x = @y) --DOES NOT WORK

--So far the simplest implementation I found is this:
select case when @x=@y then 0 else 1 end

Not great, because if my first attempt had worked I would next want to
string several expressions together like this:
select @Result = (@x=@y) and (@a=@b) and (@c=0)

Suggestions?
Perhaps a scalar-valued function, or a .NET assembly?

-Tom.

There is no BOOLEAN data type in SQL. Using CASE to implement this type of
logic is a standard way.

An alternative is to use an old method by David Rozenshtein (used to be
popular for pivoting when CASE was not available years ago). Using your
sample, it will look like this:

-- if you want to return 0 when @x = @y, otherwise 1
SELECT ABS(SIGN(@x - @y))

-- if you want to return 1 when @x = @y, otherwise 0
SELECT 1 - ABS(SIGN(@x - @y))

It is shorter to write, but I think more confusing to read and understand.
And of course, it works fine only for numeric expressions.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

>> Suggestions? <<

There are no BOOLEANs in SQL. We use predicates (search conditions)
to discover the state of the database instead of programming with
flags. You are missing one of the major differences in declarative
versus procedural programming.

What you want to do is like doing linked lists in Fortran II -- you
can fake it, but you've missed the point of the language.

There are a couple ways to set your @Result flag.

--Use a CASE statement to set the @Result flag
SELECT @RESULT = CASE
WHEN @x = @y THEN 1
ELSE 0
END


--Set @Result = 0 when you declare the variable...
--then in the body of the script, switch the flag to 1 if the values
are equal
SET @Result = 0
IF (@x=@y)
SET @Result = 1

--CELKO-- wrote:

> There are no BOOLEANs in SQL. We use predicates (search conditions)
> to discover the state of the database instead of programming with
> flags. You are missing one of the major differences in declarative
> versus procedural programming.

This is an oversimplification. See
http://en.wikipedia.org/wiki/Boolean_datatype#SQL

Things get opinionated when you start dealing with NULL vs. UNKNOWN,
and "all functions with null inputs have null outputs except ISNULL
and COALESCE" vs. "if A is TRUE, then (A AND B) is TRUE, regardless
of the status of B".

In practice, there are lots of ways to implement the concept of a flag:

* New column (1/0, Y/N, T/F, whatever)
* Interpretation of one or more existing columns (e.g. date and
reason an employee was terminated)
* Eventually, it becomes worthwhile to normalize the flag-dependent
columns into a separate table (details debatable)

On 06.02.2008 16:34, Andy M wrote:
> There are a couple ways to set your @Result flag.
>
> --Use a CASE statement to set the @Result flag
> SELECT @RESULT = CASE
> WHEN @x = @y THEN 1
> ELSE 0
> END

You can even save a "=" or does SQL Server not support this form of case?

select @result = case @x when @y then 1 else 0 end

Kind regards

robert

>>You can even save a "=" or does SQL Server not support this form of case? <<

Let me do a "cut & paste" here:

The CASE expression is an *expression* and not a control statement;
that is, it returns a value of one data type. SQL-92 stole the idea
and the syntax from the ADA programming language. Here is the BNF for
a <case specification>:

<case specification> ::= <simple case> | <searched case>

<simple case> ::=
CASE <case operand>
<simple when clause>...
[<else clause>]
END

<searched case> ::=
CASE
<searched when clause>...
[<else clause>]
END

<simple when clause> ::= WHEN <when operand> THEN <result>

<searched when clause> ::= WHEN <search condition> THEN <result>

<else clause> ::= ELSE <result>

<case operand> ::= <value expression>

<when operand> ::= <value expression>

<result> ::= <result expression> | NULL

<result expression> ::= <value expression>

The searched CASE expression is probably the most used version of the
expression. The WHEN ... THEN ... clauses are executed in left to
right order. The first WHEN clause that tests TRUE returns the value
given in its THEN clause. And, yes, you can nest CASE expressions
inside each other. If no explicit ELSE clause is given for the CASE
expression, then the database will insert a default ELSE NULL clause.
If you want to return a NULL in a THEN clause, then you must use a
CAST (NULL AS <data type>) expression. I recommend always giving the
ELSE clause, so that you can change it later when you find something
explicit to return.

The <simple case expression> is defined as a searched CASE expression
in which all the WHEN clauses are made into equality comparisons
against the <case operand>. For example

CASE iso_sex_code
WHEN 0 THEN 'Unknown'
WHEN 1 THEN 'Male'
WHEN 2 THEN 'Female'
WHEN 9 THEN 'N/A'
ELSE NULL END

could also be written as:

CASE
WHEN iso_sex_code = 0 THEN 'Unknown'
WHEN iso_sex_code = 1 THEN 'Male'
WHEN iso_sex_code = 2 THEN 'Female'
WHEN iso_sex_code = 9 THEN 'N/A'
ELSE NULL END

There is a gimmick in this definition, however. The expression

CASE foo
WHEN 1 THEN 'bar'
WHEN NULL THEN 'no bar'
END

becomes

CASE WHEN foo = 1 THEN 'bar'
WHEN foo = NULL THEN 'no_bar' -- error!
ELSE NULL END

The second WHEN clause is always UNKNOWN.

The SQL-92 Standard defines other functions in terms of the CASE
expression, which makes the language a bit more compact and easier to
implement. For example, the COALESCE () function can be defined for
one or two expressions by

1) COALESCE (<value exp #1>) is equivalent to (<value exp #1>)

2) COALESCE (<value exp #1>, <value exp #2>) is equivalent to

CASE WHEN <value exp #1> IS NOT NULL
THEN <value exp #1>
ELSE <value exp #2> END

then we can recursively define it for (n) expressions, where (n >= 3),
in the list by

COALESCE (<value exp #1>, <value exp #2>, . . ., n), as equivalent to:

CASE WHEN <value exp #1> IS NOT NULL
THEN <value exp #1>
ELSE COALESCE (<value exp #2>, . . ., n)
END

Likewise, NULLIF (<value exp #1>, <value exp #2>) is equivalent to:

CASE WHEN <value exp #1> = <value exp #2>
THEN NULL
ELSE <value exp #1> END

It is important to be sure that you have a THEN or ELSE clause with a
data type that the compiler can find to determine the highest data
type for the expression.

A trick in the WHERE clause is use it for a complex predicate with
material implications.

WHERE CASE
WHEN <search condition #1>
THEN 1
WHEN <search condition #2>
THEN 1
...
ELSE 0 END = 1

Gert-Jan Strik posted some exampels of how ISNULL() and COALESCE() on
2004 Aug 19

CREATE TABLE #t(a CHAR(1));
INSERT INTO #t VALUES (NULL);
SELECT ISNULL(a,'abc') FROM #t;
SELECT COALESCE(a, 'abc') FROM #t;
DROP TABLE #t;

He always use COALESCE, with the exception of the following type of
situation, because of its performance consequences:

SELECT ...,
ISNULL((SELECT COUNT(*) -- or other aggregate
FROM B
WHERE B.key = A.key), 0)
FROM A;

Likewise, Alejandro Mesa cam up with this example:

SELECT 13 / COALESCE(CAST(NULL AS INTEGER), 2.00); -- promote to
highest type (decimal)
SELECT 13 / ISNULL(CAST(NULL AS INTEGER), 2.00); -- promote to first
type (integer)

On Tue, 05 Feb 2008 18:16:44 -0700, Tom van Stiphout
<no.spam.tom7744@cox.net> wrote:

Thanks everyone for your thoughts and suggestions. As I am reading
them I think it may be best not to simplify my scenario but spell it
out. I am certainly a big proponent of normalized database design and
I'm not using flags or procedural code without some thought as to
alternatives.
SQL2005. Database is about a Parts Catalog. We have Categories,
Subcategories etc at infinitum (typically <=5), and at the lowest
level we have about 50,000 Parts.
Both Categories and Parts may have a defined life time, so both tables
have EffectiveDate, ExpireDate, IsDeleted.
We added a computed column IsActive, which is set to:
([dbo].[fnIsActive]([EffectiveDate],[ExpireDate],[IsDeleted]))

The scalar function fnIsActive is below. I felt the procedural code
was probably not very fast, so I was looking to rewrite. Ideally
something like:
select @Return = (EffectiveDate<getdate()) AND
(ExpireDate>getdate()) AND (IsDeleted=0)
But as we have concluded that's not possible in T-SQL.
So I was looking for alternatives.

The function fnIsActive follows:
ALTER FUNCTION [dbo].[fnIsActive] (
@EffectiveDate datetime
, @ExpireDate datetime
, @IsDeleted bit
)
RETURNS bit
AS
BEGIN
DECLARE @ReturnValue bit

IF @IsDeleted = 1
BEGIN
SET @ReturnValue = 0
END
ELSE
BEGIN
--Is the current date before the effective date?
IF @ReturnValue IS NULL
BEGIN
IF GETDATE() < @EffectiveDate
BEGIN
SET @ReturnValue = 0
END
END

--Is the current date after the expiration date?
IF @ReturnValue IS NULL
BEGIN
IF GETDATE() > @ExpireDate
BEGIN
SET @ReturnValue = 0
END
END

--Must be OK
IF @ReturnValue IS NULL
BEGIN
SET @ReturnValue = 1
END
END

RETURN @ReturnValue
END

Thank you,

-Tom.


>My sproc code, simplified:
>
>declare @x int
>declare @y int
>declare @Result bit
>
>--Assign @x and @y by running some complex code.
>--For testing we will say:
>set @x = 1
>set @y = 2
>
>--Now I want to compare them, ideally this way would return true or
>false:
>select @Result = (@x = @y) --DOES NOT WORK
>
>--So far the simplest implementation I found is this:
>select case when @x=@y then 0 else 1 end
>
>Not great, because if my first attempt had worked I would next want to
>string several expressions together like this:
>select @Result = (@x=@y) and (@a=@b) and (@c=0)
>
>Suggestions?
>Perhaps a scalar-valued function, or a .NET assembly?
>
>-Tom.

>> I'm not using flags or procedural code without some thought as to alternatives. <<

Let's talk about those options. First, I turned your dialect into
Standard SQL whenever possible and your data element names into
ISO-11179 forms.

>> Database is about a Parts Catalog. We have Categories, Subcategories etc at infinitum (typically <= 5), and at the lowest level we have about 50,000 Parts. Both Categories and Parts may have a defined life time, so both tables have effective_date, expire_date, deleted_flag. We added a computed column active_flag, which is set to:

(active_flagdbo.active_flag SetActiveFlag(active_flageffective_date,
active_flagexpire_date, active_flagdeleted_flag))

The scalar function SetActiveFlag is below. I felt the procedural code
was probably not very fast, so I was looking to rewrite. Ideally
something like:

SET @return = (effective_date < CURRENT_TIMESTAMP)
AND (expire_date > CURRENT_TIMESTAMP)
AND (deleted_flag = 0) <<

That is very nice in a procedural language that has BOOLEAN data
types. Did you notice that your mindset is still stuck in your native
language? Too many parens that we used in C to force order of
execution in early compilers, and the failure to use a NOT BETWEEN
short hand are the give-away that you are still thinking in C, C++,
VB, Fortran, or whatever. Also, an SQL programmer would put part of
this into a CHECK() constraint, not DML.

>> But as we have concluded that's not possible in T-SQL. So I was looking for alternatives. <<

But you have not written SQL yet! You are still faking your
procedural language in SQL and hitting a wall. Let's take it a step
at a time, so you can see how to think in sets. I cleaned up
SetActiveFlag follows (cleaned up just a little, but still procedural
code. You seem to like to do a lot of needless IF-THEN testing rather
than just using a RETURN() when you have an answer. That is bad
procedural programming and has nothing to do with SQL; you might want
to read Gries and some of the books on Structured Programming.

CREATE FUNCTION SetActiveFlag -- notice <verb><object> naming
(@effective_date DATETIME,
@expire_date DATETIME,
@deleted_flag BIT) -- <attribute><property> naming
RETURNS BIT
AS
BEGIN
DECLARE @return_flag BIT; -- useless local variable
IF @deleted_flag = 1
SET @return_flag = 0
ELSE IF @return_flag IS NULL
IF GETDATE() < @effective_date
SET @return_flag = 0;
--Is the current date after the expiration date?
IF @return_flag IS NULL
IF GETDATE() > @expire_date
SET @return_flag = 0;
--Must be OK
IF @return_flag IS NULL
SET @return_flag = 1;
RETURN @return_flag;
END;

Here is a version using SQL instead of structured procedural code.

CREATE FUNCTION SetActiveFlag
(@effective_date DATETIME, @expire_date DATETIME, @deleted_flag BIT)
RETURNS BIT
AS
RETURN (CASE
WHEN @deleted_flag = 1 THEN 0
WHEN CURRENT_TIMESTAMP NOT BETWEEN @effective_date AND @expire_date
THEN 0 ELSE 1 END);

This still stinks; we know that (deleted_flag, active_flag) have to
have opposite values, so at least one of them is redundant. We don't
do redundancy in an RDBMS. In fact, both of them are redundant; the
expiration date tells us if something is active.

This should be handled in the DDL that you never posted. SQL is
declarative. So some wild guesses are in order:

>> Both Categories and Parts may have a defined life time .. <<

I find that a bit weird; books come and go from a library, but the
Dewey Decimal Classification system remains pretty stable. Can I
assume that every part falls into a category? Do you set future
expiration dates? Lot of questions here. But let's start with a
typical history table:

CREATE TABLE InventoryHistory
(part_nbr CHAR(15) NOT NULL,
effective_date DATETIME NOT NULL,
PRIMARY KEY (part_nbr, effective_date),
expire_date DATETIME, -- null means current
CHECK (effective_date < expire_date),
part_category CHAR(10) NOT NULL
REFERENCES PartsCategories(part_category)
ON UPDATE CASCADE,
etc.);

Then you create an updatable VIEW of the current Inventory and use
it:

CREATE VIEW Inventory (...)
AS
SELECT ..
FROM InventoryHistory
WHERE CURRENT_TIMESTAMP <= COALESCE (expire_date,
CURRENT_TIMESTAMP);

The part categories can be in a nested sets model table (Google it if
you don't know this standard pattern or get a copy of TREES &
HIERARCHIES IN SQL). I am not sure how to handle the changing
categories, since I have no idea what the rate of change is or even
how they change (i.e. in Dewey Decimal, logic moved from the 100's
(Philosophy) to the 500's (math); new categories were created under
the 500's (nanotech); etc.) so the safest way is to create a forest
table.

Each tree in the forest will be at least 50,001 rows, according to
your specs, so I will guess they fall into 250 categories in the
average tree (I am not going to do the Bell numbers or what that
series is called for the number of possible trees). This is not big
on modern computer, and partitioning the table by time periods should
really help.

CREATE PartCategories -- leaving out a lot of constraints
(part_nbr CHAR(15) NOT NULL,
part_category CHAR(15) NOT NULL,
lft INTEGER NOT NULL CHECK (lft > 0),
rgt INTEGER NOT NULL CHECK (rgt > 0),
C HECK (lft < rgt), -- assume all the usual nested sets constraints
effective_date DATETIME NOT NULL
expire_date DATETIME, -- null means current
CHECK (effective_date < expire_date),
PRIMARY KEY (effective_date, part_category, part_nbr),
etc.)

The (effective_date, expire_date) pair defines the heirarchy during
that time period -- a tree in a forest identified by its effective
date. Again, you can use a VIEW to find the current hierarchy. Now
add a calendar table and use BEWTWEEN predicates with (effective_date,
expire_date) pairs in the history tables to determine the state of the
database for any day you have.

You can now write very simple SQL and app code using VIEWs because all
the effort has been moved to the DDL. You do not have to do data
integrity checking in hundred of programs. Your SQL will port and
should scale. But again, this is skeleton based on your narrative and
a lack of specs.

On 07.02.2008 01:17, --CELKO-- wrote:
>>> You can even save a "=" or does SQL Server not support this form of case? <<
>
> Let me do a "cut & paste" here:
>
> The CASE expression is an *expression* and not a control statement;

I did not claim that it is a control statement and everybody seems
pretty aware the status of "case".

Cheers

robert