473,246 Members | 1,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,246 software developers and data experts.

trouble porting a trivially simple function - with declared variables

Ted
Here is one such function:

CREATE FUNCTION my_max_market_date () RETURNS datetime
BEGIN
DECLARE @mmmd AS datetime;
SELECT max(h_market_date) 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

Aug 4 '06 #1
6 1439
Ted
OOPS, I should have also said I'm using MS SQL Server 2005 (the
developer's edition)

Ted

Aug 4 '06 #2
Ted (r.*********@rogers.com) writes:
Here is one such function:

CREATE FUNCTION my_max_market_date () RETURNS datetime
BEGIN
DECLARE @mmmd AS datetime;
SELECT max(h_market_date) 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_date) FROM holdings_tmp;
SET @mmmd = (SELECT max(h_market_date) FROM holdings_tmp);
RETURN (SELECT max(h_market_date) 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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Aug 4 '06 #3
>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.

Aug 5 '06 #4

CREATE FUNCTION my_max_market_date ( ) RETURNS DATETIME
RETURN (SELECT MAX(h_market_date) 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.

Aug 5 '06 #5
Ted

--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

Aug 7 '06 #6
--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...

Aug 7 '06 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: Penn Markham | last post by:
Hello all, I am writing a script where I need to use the system() function to call htpasswd. I can do this just fine on the command line...works great (see attached file, test.php). When my...
5
by: Edward | last post by:
Years ago I wrote an app in VB 4.0 16 bit. The program is still selling, but the clients want to upgrade to 32 bit. Should I go for VB 4.0 32 bit, or version 5, or version 6? There is only a...
2
by: Jeff | last post by:
/* -------------------------------------------------------------------------- Hello, I was experimenting with class templates and specializing member functions and came across a simple problem...
4
by: Chris Travers | last post by:
Hi all; A few years ago, I set about porting a PHP application from MySQL to PostgreSQL, after realizing that MySQL wasn't going to be able to handle it. In order to do this, I built a light,...
3
by: Rob C | last post by:
I am rebuilding a VC++ w/ MFC Project from Visual Studio V6 in Visual Studio .NET 2003. There are 2 errors that I am getting that I do not get when I build using V6. 1) problem with my...
3
by: Olivier BESSON | last post by:
Hello, I have a web service of my own on a server (vb.net). I must declare it with SoapRpcMethod to be used with JAVA. This is a simple exemple method of my vb source : ...
12
by: Steve Blinkhorn | last post by:
Does anyone know of a way of accessing and modifying variables declared static within a function from outside that function? Please no homilies on why it's bad practice: the context is very...
8
by: Bern McCarty | last post by:
I have a simple ref class in its own namespace that needs to coexist with a legacy typedef alias for "unsigned int" in the global namespace that has the identifier as itself. Everything compiles...
20
by: Ruud | last post by:
Hallo allemaal, I created a program where I opened a file to read from. For various reasons I had to split up this program in several modules. Variables declared in one module are made...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.