trouble porting a trivially simple function - with declared variables

Giganews Newsgroups
Subject: trouble porting a trivially simple function - with declared variables
Posted by:  Ted (r.ted.bye…@rogers.com)
Date: 4 Aug 2006

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

Replies