trouble porting a trivially simple function - with declared variables

[Ted]

Here is one such function:

CREATE FUNCTION my_max_market_d ate () RETURNS datetime
BEGIN
DECLARE @mmmd AS datetime;
SELECT max(h_market_da te) INTO @mmmd FROM holdings_tmp;
RETURN @mmmd;
END

One change I had to make, relative to what I had working in MySQL, was
to insert 'AS' between my variable and its type. Without 'AS', MS SQL
insisted in telling me that datetime is not valid for a cursor; and I
am not using a cursor here. The purpose of this function is to
simplify a number of SQL statements that depend on obtaining the most
recent datetime value in column h_market_date in the holdings_tmp
table.

The present problem is that MS SQL doesn't seem to want to allow me to
place that value in my variable '@mmmd'. I could do this easily in
MySQL. Why is MS SQL giving me grief over something that should be so
simple. I have not yet found anything in the documentation for SELECT
that could explain what's wrong here. :-(

Any ideas?

Thanks

Ted

[Ted]

Re: trouble porting a trivially simple function - with declared variables

OOPS, I should have also said I'm using MS SQL Server 2005 (the
developer's edition)

Ted

[Erland Sommarskog]

Re: trouble porting a trivially simple function - with declared variables

Ted (r.ted.byers@ro gers.com) writes:

Here is one such function:
>
CREATE FUNCTION my_max_market_d ate () RETURNS datetime
BEGIN
DECLARE @mmmd AS datetime;
SELECT max(h_market_da te) INTO @mmmd FROM holdings_tmp;
RETURN @mmmd;
END
>
One change I had to make, relative to what I had working in MySQL, was
to insert 'AS' between my variable and its type. Without 'AS', MS SQL
insisted in telling me that datetime is not valid for a cursor; and I
am not using a cursor here.

Huh? AS is not mandatory in variable declarations, and I never use it
myself.

The purpose of this function is to simplify a number of SQL statements
that depend on obtaining the most recent datetime value in column
h_market_date in the holdings_tmp table.

Maybe, but be careful with scalar functions with data access. If you
put a call to a scalar UDF in a WHERE clause that has many rows to
filter, the effect on performance can be outrageous.

The present problem is that MS SQL doesn't seem to want to allow me to
place that value in my variable '@mmmd'. I could do this easily in
MySQL. Why is MS SQL giving me grief over something that should be so
simple. I have not yet found anything in the documentation for SELECT
that could explain what's wrong here. :-(

Of course, as long as insist on inventing your own syntax (and that
includes trying proprietary syntax from MySQL) you will have a hard
time.

On SQL Server SELECT INTO creates a table, but you cannot have variable
for the table name, nor a table variable. It has to be an identifier.
But you cannot use SELECT INTO in a function anyway.

The syntax you are looking for is one of:

SELECT @mmmd = max(h_market_da te) FROM holdings_tmp;
SET @mmmd = (SELECT max(h_market_da te) FROM holdings_tmp);
RETURN (SELECT max(h_market_da te) FROM holdings_tmp);

SQL Server comes with an extensive Online documentation, and while it may
be difficult to know where to start looking, it can be a good idea to
start to use Books Online. It pays off in the long run.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[--CELKO--]

Re: trouble porting a trivially simple function - with declared variables

>Of course, as long as insist on inventing your own syntax (and that includes trying proprietary syntax from MySQL) you will have a hard time. <<

Actually, it is the other way around; SELECT.. INTO is called a
singleton select in Standard SQL and SQL Server is the invented syntax.

[--CELKO--]

Re: trouble porting a trivially simple function - with declared variables


CREATE FUNCTION my_max_market_d ate ( ) RETURNS DATETIME
RETURN (SELECT MAX(h_market_da te) FROM Holdings_Tmp);

Why waste space on a local variable? SQL Server's SELECT.. INTO syntax
is dialect and violates the Standards and the optional DECLARE..
[AS].. syntax is also dialect -- the SQL/PSM would not uses the [AS]
because it is for creating alias names, not allocating scalars.

[Ted]

Re: trouble porting a trivially simple function - with declared variables


--CELKO-- wrote:

Of course, as long as insist on inventing your own syntax (and that includes trying proprietary syntax from MySQL) you will have a hard time. <<

>
Actually, it is the other way around; SELECT.. INTO is called a
singleton select in Standard SQL and SQL Server is the invented syntax.

This illustrates one aspect of what I am finding so frustratng. I have
found very little information on exactly what the SQL standard actually
says. Most of my programming experience has involved the use of
FORTRAN, C/C++ or Java. In C++, for example, one could not go wrong
using classic sources such as Stroustrup's description of the language,
Josuttis' treatment of the STL and Lippman's treatment of the object
model in C++; and of course there are a good number of other
authorities whose books on aspects of C++ programming are of
considerable value. In my experience, though, there was not a lot of
difference among available compilers WRT standard compliance, at least
WRT core C++. The diiferences I found painful generally involved
periferal features that were little used for the longest time: a
consequence of some of these being so difficult. On any of these
issues, language lawyers would quarrel incessantly, while pragmatic
developers would instead say that their development tools are the final
arbiters of what is correct. This, in my current context, would amount
to saying that the RDBMS I am using at the time is the final arbiter of
what SQL syntax is correct. In Java, it is easier in that Sun's JRE is
the only arbiter of what is correct. ;-)

WRT "invented syntax", possibly better named as "extensions to the
standard", I have sometimes used such extensions provided by certain
tool vendors, and this was because they were very useful. In each
case, though, I knew they were extensions, and expected to have to
redevelop some parts of my code should I need to replace one suite of
development tools by another. In each of these cases, the vendor
documented these features as extensions beyond what the standard
specified.

My main problem right now is that none of my database tools relate what
they support to the SQL standard. They describe, to varying degrees of
success, what they do, or what SQL syntax they'll accept as correct,
but they generally don't talk about what is standard SQL and what is
their own extension. Therefore, my question to both of you is this:
"Has anyone, in recent years, examined what each of the common
proprietary RDBMS and each of the common open source RDBMS products
have done WRT implementing the standard, identifying where they have it
right or wrong and where they've provided extensions?" Or is it the
case that so many database developers are so locked into a specific
product they can afford to regard whatever theat product does is
defined to be correct regardless of what the standard may say, so
there'd be no interest in the sort of resource I am after?

Cheers,

Ted

[Alexander Kuznetsov]

Re: trouble porting a trivially simple function - with declared variables

--CELKO-- wrote:

Of course, as long as insist on inventing your own syntax (and that includes trying proprietary syntax from MySQL) you will have a hard time. <<

>
Actually, it is the other way around; SELECT.. INTO is called a
singleton select in Standard SQL and SQL Server is the invented syntax.

Well, DB2 UDB is very good at sticking to the standard. Look at their
market share and revenues as compared to less compliant Oracle and SQL
Server. After all, maybe "inventing your own syntax" has been, and is,
very profitable...