Require help writing a function

Hi there
I am trying to write a function which will return the Maximum value in a
field of a given table and have run into problems.
I want to pass the name of the field and the name of the table to the
function, have it find the Max value and then add 1 to that number and
return the result.
After doing some reading I thought a Simple Scalar Function would be the way
to go but I just cannot make it work.
Could anyone help me please.
Thanks
June
hi June,
"June Macleod" <junework@.hotmail.com> ha scritto nel messaggio
news:esYBukT4EHA.2568@.TK2MSFTNGP10.phx.gbl
> Hi there
> I am trying to write a function which will return the Maximum value
> in a field of a given table and have run into problems.
> I want to pass the name of the field and the name of the table to the
> function, have it find the Max value and then add 1 to that number and
> return the result.
> After doing some reading I thought a Simple Scalar Function would be
> the way to go but I just cannot make it work.
>
what you want to do is known as Dynamic SQL, which provides poor
performances and security holes toubles...
for your informatgion, please have a look at
http://www.sommarskog.se/dynamic_sql.html for a great explanation of it's
uses and related troubles... and something like
CREATE FUNCTION dbo.udfNextValue (
@.col VARCHAR(10) ,
@.tbl VARCHAR (10)
) RETURNS INT
AS BEGIN
DECLARE @.cmd NVARCHAR
DECLARE @.Value INT
SET @.cmd ='SELECT @.Value = ISNULL(MAX(' + @.col + ') , 0) + 1 FROM ' + @.tbl
EXEC sp_executesql @.cmd, N'@.Value INT OUTPUT', @.Value OUTPUT
RETURN (@.Value)
END
is unfortunately not permitted within user defined function...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply