Posted by Erland Sommarskog on 08/04/06 22:00

Ted (r.ted.byers@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, esquel@sommarskog.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

[Back to original message]