Coding in standard SQL ?

[Steve]

Hi;

Is there an **easy** way to tell tsql apart from standard sql?

Will sqlserver run scripts written only in standard sql?

What about variable definitions?

Thanks in advance

Steve

[Serge Rielau]

Variable definitions are the first thing that kills you.
SQL/PSM uses the same namespace for variables as for columns, no @.
If you want to write portable SQL you'd have to stay away from any
procedural logic in SQL because no two DBMS have the same implementation:
PL/SQL (Oracle), T-SQL (MS, Sybase), SPL (Informix), SQL/PSM (DB2)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Steve]

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<c7**********@hanover.torolab.ibm.com>...

Variable definitions are the first thing that kills you.
SQL/PSM uses the same namespace for variables as for columns, no @.
If you want to write portable SQL you'd have to stay away from any
procedural logic in SQL because no two DBMS have the same implementation:
PL/SQL (Oracle), T-SQL (MS, Sybase), SPL (Informix), SQL/PSM (DB2)

Cheers
Serge

LOL!

Thanks for preventing me from wasting my time :)

Steve

[Serge Rielau]

Well, if you try to write standard SQL statements at least it's one
worry less.
Mapping the procedural blurp isn't that bad and most vendors support
migration tools to steal each others customers.

I would steer clear of scalar subqueries (queries which are expected to
return a single row and a single column) if you are NOT sure they return
a single row. The SQL standard requires a DBMS to raise an error if more
than one row is retrieved.
TSQL will happily return "a" row, IIRC.

use regular JOIN syntax instead of the short forms,

go easy on TOP and it's friends (it maps to FETCH FIRST n ROWS which is
trivial enough).

GROUP BY is no substitute to ORDER BY

Avoid setting the rowcount (?) environment variable like the plague.

Never depend on INSERT order or order in the table in general.

UPDATE/DELETE FROM isn't that bad. Informix supports it and both Oracle
and DB2 support SQL Standard MERGE which maps well enough.
Just make sure you join on keys and don't write the same row twice.

All hope is lost I fear to ever agree on how to return updated rows.
Yukon: UPDATE INTO, Oracle: UPDATE with RETURN, DB2: SELECT FROM UPDATE
(Dear MS folks: Is it too late to change? Bad timing I s'pose *sigh*)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[John Gilson]

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:c7**********@hanover.torolab.ibm.com...

Well, if you try to write standard SQL statements at least it's one
worry less.
Mapping the procedural blurp isn't that bad and most vendors support
migration tools to steal each others customers.

I would steer clear of scalar subqueries (queries which are expected to
return a single row and a single column) if you are NOT sure they return
a single row. The SQL standard requires a DBMS to raise an error if more
than one row is retrieved.
TSQL will happily return "a" row, IIRC.
T-SQL will indeed raise an error here that looks like:

Server: Msg 512, Level 16, State 1, Line XXX
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= ,, >= or when the subquery is used as an expression.
I know this to be the behavior since at least 7.0.

--
JAG
use regular JOIN syntax instead of the short forms,

go easy on TOP and it's friends (it maps to FETCH FIRST n ROWS which is
trivial enough).

GROUP BY is no substitute to ORDER BY

Avoid setting the rowcount (?) environment variable like the plague.

Never depend on INSERT order or order in the table in general.

UPDATE/DELETE FROM isn't that bad. Informix supports it and both Oracle
and DB2 support SQL Standard MERGE which maps well enough.
Just make sure you join on keys and don't write the same row twice.

All hope is lost I fear to ever agree on how to return updated rows.
Yukon: UPDATE INTO, Oracle: UPDATE with RETURN, DB2: SELECT FROM UPDATE
(Dear MS folks: Is it too late to change? Bad timing I s'pose *sigh*)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Erland Sommarskog]

John Gilson (ja*@acm.org) writes:

T-SQL will indeed raise an error here that looks like:

Server: Msg 512, Level 16, State 1, Line XXX
Subquery returned more than 1 value. This is not permitted when the
subquery follows =, !=, <, <= ,

, >= or when the subquery is used as an expression.

I know this to be the behavior since at least 7.0.

It was the same in 4.x and 6.x too.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Steve (st**********@yahoo.com) writes:

Is there an **easy** way to tell tsql apart from standard sql?
You could try SET FIPS_FLAGGER ON. I believe this command warns you
about non-standard SQL things. I have never tried it myself, though.
Will sqlserver run scripts written only in standard sql?

Depends on which standard you mean. ANSI has revised the SQL standard
a couple of times, and each revision has several levels. It is possible
that SQL Server conforms fully to the entry level of ANSI-89, but generally
you should not assume just because it's in the ANSI that SQL Server
supports it. SQL Server has taken quite a few things from ANSI, but
has blissfullly ignored others.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Serge Rielau]

Way to go, must be a habit that got left with Sybase then.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[--CELKO--]

>> Is there an **easy** way to tell tsql apart from standard sql? <<

1) Go to the Mimer website and use their on-line validator tool.
2) Write code with the FIPS Flager turned on. If you are doing any
federal work, you are doing this already.

Will sqlserver run scripts written only in standard sql? <<
Mostly, but not always.
What about variable definitions? <<

Watch your datatypes and avoid the proprietary ones. Standard SQL
uses a colon in place of a leading @ sign.

Having written five books and 700+ articles with nothing but Standard
SQL, if you learn what you are doing, it is not that much trouble to
move to a dialect. It is much harder going from a dialect to Standard
SQL. You might also look at Vembu, a translation tool.