are all expressions an expression?

[Peter J. Veger]

I am trying to upsize a mdb-application to an adp-application (20 tables,
170 queries, 20 forms, 80 reports, 26 class modules, 1 non-class module,
ADO)
Only a small part of the queries were translated ---- primary reason: usage
of VBA-functions from the non-class module.
I am now busy to translate manually the queries to T-SQL and that
translation goes very well but for one problem.

The following in-line procedure (simplified) is not accepted ----- gives an
ADO error:
===============
CREATE PROCEDURE Statistics(@RefDatum smalldatetime)
RETURNS table
AS RETURN (
SELECT Persons.PID AS PID,
Persons.Email IS NULL AS HasEmail,
EXISTS( SELECT * FROM PersonsBoats WHERE Persons.PID = PersonsBoats.PID) AS
HasBoat
FROM Persons
WHERE Year(Persons.SomeDate)=Year(@RefDatum)
================

The bit-valued expressions
expr IS NULL
and
EXISTS(SELECT ......)
are accepted in Access-ADO-mdb but not in Access-ADO-adp.
Also brackets around these expression don't have any effect.

From the Books-Online:
1) The "unary postfix" operators IS [NOT] NULL do not occur among the
Operators but the text of "IS [NOT] NULL" makes one belief that they build
expressions, e.g. their use in a WHERE clause:
...WHERE ..... OR advance IS NULL....
2) EXISTS(subquery) is mentioned in "Operators", but subquery is in general
not an expression --- "Expressions" allows only scalar_subquery!

What is wrong?
Is there a possibility to circumvent these vague restrictions?

 

[Sylvain Lafontaine]

For Persons.Email is Null, you must use a CASE:

select Case When Persons.Email is null Then 1 Else 0 End as HasEmail ...

Same thing for your EXIST clause:

... Case When EXISTS(SELECT * ... ) Then 1 Else 0 End

In SQL, logical values can only be used inside logical expressions and
cannot be directly returned as such; unlike other language as C/C++ and VB.

S. L.