Re: trouble porting a trivially simple function - with declared variables
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 4 Aug 2006 22:00:12 +0000 (UTC)
Ted (r.ted.byers@xxxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- Follow-Ups:
- References:
- Prev by Date: Re: Having Problem While Importing a Text File
- Next by Date: Re: 2005: cannot to connect from .NET
- Previous by thread: Re: trouble porting a trivially simple function - with declared variables
- Next by thread: Re: trouble porting a trivially simple function - with declared variables
- Index(es):
Relevant Pages
|
