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

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

[quoted text, click to view]

LOL!

Thanks for preventing me from wasting my time :)

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

[quoted text, click to view]

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 =, !=, <, <= ,
[quoted text, click to view]

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

--
JAG

[quoted text, click to view]

John Gilson (jag@acm.org) writes:
[quoted text, click to view]

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

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Steve (stevesusenet@yahoo.com) writes:
[quoted text, click to view]

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.

[quoted text, click to view]

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, sommar@algonet.se

Books Online for SQL Server SP3 at

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

--
Serge Rielau
DB2 SQL Compiler Development

[quoted text, click to view]

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.

[quoted text, click to view]

Mostly, but not always.

[quoted text, click to view]

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