Ok, Mom, I'm sure there are others that deal better with MsSql. I've been
doing some consultancy on MsSql for the last 5 years but I'm not an expert
on procedures... in fact, this is the part I've touch less:

- CASE statement. Do I need to explain? Hope that not. In compiled languages
like Delphi, CASE allows for compiler optimizations. Also, it can be easier
to code for developers when facing several decisions based on the same
expression.

- There's no selectable sprocs, but a sproc can return a CURSOR.

- That's interesting, it resembles our discussion about db-wide triggers:
«
Automatic Execution of Stored Procedures:
When you mark stored procedures for automatic execution, these stored
procedures are executed every time Microsoft® SQL Server™ starts.
»

- At least IB detects these issues that in MsSql are left to the
developer... umh, I will include it among the IB abilities:
«
Note Changing the name or definition of a stored procedure can cause any
dependent objects to fail when executed if those dependent objects are not
also updated to reflect the changes made to the stored procedure.
Note Renaming a stored procedure does not change the name of the stored
procedure in the text of the procedure’s definition. To change the name of
the stored procedure in the definition, modify the stored procedure
directly.
[snip and this is worse than VB] This process is called deferred name
resolution because objects referenced by the stored procedure need not exist
when the stored procedure is created, but only when it is executed.
»

- @@NESTLEVEL (T-SQL)
Returns the nesting level of the current stored procedure execution
(initially 0).
Interbase uses this information internally to stop a recursive sproc after
1000 calls, how about making it public?

- @@ROWCOUNT (T-SQL)
Returns the number of rows affected by the last statement.
Currently, IB only provides these values for the Embedded SQL or DSQL
interfaces.

- @@TRANCOUNT (T-SQL)
Returns the number of active transactions for the current connection.
Maybe a solution looking for a problem? Not sure.

- @@VERSION (T-SQL)
Returns the date, version, and processor type for the current installation
of Microsoft® SQL Server™.
I think IB can make available the major and minor versions or at least the
major version and the ODS version, so sprocs can make some decisions based
on those values. I want the dialect to be made available, too.

- We already have USER variable. This is standard, I think. We need ROLE or
ROLE_NAME, too. It would be useful if in the case new non-standard system
vars are included, they are preceded by some sign (MsSql uses @), so we
don't forbid new plain words. Making "#version" a reserved word inside
sprocs causes less problems than making "version" a reserved word.

- Extended Stored Procs are allowed. They are procedures written in an
external language and compiled in a DLL. Don't confuse them with UDFs,
because Extended Procs can return several parameters or result sets... maybe
this is the Java-inside-IB idea of Jim.

- Identifiers, including variables' names, parameters' names and procs and
tables' names can go up to 128 characters. IB limit of 31 chars is annoying
for people that come from Access and other databases.

- Stored procedures can have optional parameters if one defines a default
for those parameters. The default can be NULL, too. The default allows for
wilcards that can be used by the LIKE operator.
There's no counterpart in IB yet. Either you pass all parameters or fail in
compilation time. Also, a procedure calling another NEEDS to include the
RETUTRNING_VALUES part if the other proc returns values, even if the caller
is interested only in the side effect of the called proc.

- Depending on available memory, the maximum size of a stored procedure is
128 MB.
IB only allows for 32K. I don't need 128 MB to match MsSql, but at least
128 KB, in case of emergencies...a few times I've hit the current IB
maximum.

- If you create a private temporary table inside a stored procedure, the
temporary table exists only for the purposes of the stored procedure; it
disappears when you exit the stored procedure.
In IB, it can't be done. However, it's needed for complex task where
selectable procedures fall short, for example multi-passes over a recordset
before returning it. Also, this facility can help with server-side crosstab
queries (matrix transposition).

- That's really incredible and it means you can't hide anymore your procs'
source in Sql7. Also, I think the MS claimed advantage already exists on IB:
query plans for sprocs are not fixed. People have observed that adding or
dropping indexes affect the way an IB sproc behaves:
«Microsoft® SQL Server™ version 7.0 changes the way stored procedures and
triggers are executed. [snip] SQL Server 7.0 stores only the source for
stored procedures and triggers. When a stored procedure or trigger is first
executed, the source is compiled into an execution plan. If the stored
procedure or trigger is again executed before the execution plan is aged
from memory, the relational engine detects the existing plan and reuses it.
If the plan has aged out of memory, a new plan is built.»

- MsSql's procedures can alter data on tables on other databases. Triggers
can make references to other databases. FK relationships can target other
databases on the same server. Procedures can connect even to remote servers.
Apart from QLI, this is science fiction in IB still.

- You can group stored procedures of the same name. The syntax to call one
of these is really clumsy, example taken from the help file:
EXECUTE my_proc;2
but at least there's some support. IB doesn't know anything about
overloading on procedures.

- Helen's trick with IB5.1 works on MsSql: you can issue a complex SQL's
SELECT statement and the resultset is returned to the client that executed
the stored proc.

- The data_type in a proc is any system-supplied or user-defined data type.
A variable cannot be of text, ntext, or image data type.
I have tried successfully a NCHAR local variable and a NCHAR parameter in
IB procedures, but MsSql accepts proprietary user-defined types to define
variables. (ANSI-SQL DOMAINS in Interbase; MsSql doesn't understand
DOMAINS.) In IB, a domain is not a valid type for a variable. [OT: of
course, there's no CREATE DOMAIN statement in MsSql, you should rely instead
on the system proc sp_addtype.]

- Rights propagation: correct me if I'm wrong, but in MsSql, if procA has
rights over tableA and it calls procB and procB executes in turn procC and
so on, neither procB or procC needs rights over tableA, because procA's
rights are propagates. Whether this is ANSI behavior is something I want
clarified, but it's useful on databases with a lot of sprocs.
In Interbase, procedure's rights are not propagated, as a Polish man has
complained in the kinobi newsgroups.

- In procedures (and even in DSQL, not sure), MsSql allows for CASE
expressions inside SUM and AVG, as the argument for these functions. I don't
know if this is totally out of any standard, but it helps again for a niche
"market": crosstab queries.

- Modulo and bitwise operators. Only available in IB through UDFs, I think
at least modulo should be built-in.

- Break or leave. As I understand, BLR itself understands this instruction,
but DSQL doesn't expose it.

- Bulk insert dumps a data file into a table or view. It resembles the
external table mechanism, but it's higher level IMHO. In IB, bulk insert
could mean to automate the deactivation/activation of indexes and ensure
maximum performance for adding new data to tables.

- Trimming of chars longer than expected. In IB, this generates the dreaded
"arithmetic overflow or string truncation" error msg.

- Coalesce to deal with NULLs. Limited counterpart with UDFs. Also, UDFs
need an official way to signal NULLs.

OT but IB uses system var USER... is USER or CURRENT_USER the standard?

Excuse me for writing Interbase without the capitalized "b", this is the
reason I prefer to write IB and avoid problems.
;-)

I'm preparing an extensive comparison of types between Monster-Server (aka
SqlServer) and flea-Server (aka Interbase). I didn't wanted to offend IB, I
only wanted to mean it's lightweight.

C.

> -----Original Message-----
> From: Ann Harrison [mailto:harrison@...]
> Sent: Lunes 26 de Junio de 2000 12:37
> To: IB-Conversions@egroups.com; IB-Conversions@egroups.com
> Subject: Re: [IB-Conversions] We are not alone
>
>
>
> >
> >Welcome to all - it's pleasing to see some familiar "faces",
> sleeves rolled
> >up, as ever. <g>
>
> Something that would be interesting would be a list of
> SQLServer language extensions, particularly the useful
> ones.
>
> Ann