|
|
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
Navigation:
[Reply to this message]
|