UPDATE in a UDF

[Liz]

I'm trying to create a SQL2005 UDF which will update a table and return the
value of the updated column ... and having no luck whatsoever; this is the
basic idea of what I'm trying to do but haven't gotten done quite yet:

CREATE FUNCTION UpdateAndGetValue()
RETURNS int
AS

BEGIN
declare @inextInvoice int
UPDATE invoices SET idate = '10/1/2007', nextInvoice = nextInvoice +1
OUTPUT INSERTED.nextInvoice
SET @inextInvoice = ????
RETURN @inextInvoice
END

How can I access the OUTPUT value and return it? Can I even do this kind of
UPDATE within a UDF?

Thanks for any help ..

L

[Alex Kuznetsov]

You cannot modify anything in a UDF.

[Russell Fields]

Liz,

UDFs are (alas) unable to UPDATE. They are read-only functions. You could
convert your code to a stored procedure.

RLF

"Liz" <l...@tiredofspam.com> wrote in message
news:OXkG1fpB...@TK2MSFTNGP05.phx.gbl...

[Jim Underwood]

Updates are not allowed in functions, unless they are against local table
variables. Try a stored procedure instead.

create procedure UpdateAndGetValue()
as

UPDATE invoices SET idate = '10/1/2007', nextInvoice = nextInvoice +1

select nextInvoice from invoices
go

However, the code you are using will update every row in the invoices table.
Why would you want to do this, or is this simply a scaled down example fo
what you are trying to do?

Also, your date format is language dependent and will cause issues if run
under different langauge settings. It is best to use language neutral
formats.
http://www.karaszi.com/SQLServer/info_datetime.asp

Let us know exactly what you are trying to do, and include DDL and sample
data, and soemone can give you suggestions on how best to accomplish it.
For examples of DDL and sample data:
http://www.aspfaq.com/etiquette.asp?id=5006

"Liz" <l...@tiredofspam.com> wrote in message
news:OXkG1fpB...@TK2MSFTNGP05.phx.gbl...
>

[Alex Kuznetsov]

On Oct 4, 10:18 am, "Russell Fields" <russellfie...@nomail.com> wrote:
> Liz,
>
> UDFs are (alas) unable to UPDATE. They are read-only functions.

Russell,

if UDFs were able to update, that would lead to a lot of unpleasant
side effects, so it is better that UDFs are read only.

[Russell Fields]

I agree, my 'Alas' was sympathy for Liz, not a desire to respecify UDFs. -
RLF
"Alex Kuznetsov" <alk...@gmail.com> wrote in message
news:1191519954.0...@k79g2000hse.googlegroups.com...

[Kalen Delaney]

Actually, an inline table valued function can be updated.

Here's an example:

USE pubs
SELECT * INTO newtitles FROM titles
GO

CREATE function gettitles(@price money)
RETURNS table
AS
RETURN (SELECT title_id, type, price, title, pub_id
FROM newtitles
WHERE price < @price)
GO

SELECT * FROM dbo.gettitles(10)
GO
UPDATE dbo.gettitles(10)
SET price = price + 5
GO
SELECT * FROM dbo.gettitles(10)
GO

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

"Alex Kuznetsov" <alk...@gmail.com> wrote in message
news:1191519954.0...@k79g2000hse.googlegroups.com...

[Alex Kuznetsov]

On Oct 4, 1:02 pm, "Kalen Delaney" <replies@public_newsgroups.com>
wrote:

> Actually, an inline table valued function can be updated.
>
> Here's an example:
>
> USE pubs
> SELECT * INTO newtitles FROM titles
> GO
>
> CREATE function gettitles(@price money)
> RETURNS table
> AS
> RETURN (SELECT title_id, type, price, title, pub_id
> FROM newtitles
> WHERE price < @price)
> GO
>
> SELECT * FROM dbo.gettitles(10)
> GO
> UPDATE dbo.gettitles(10)
> SET price = price + 5
> GO
> SELECT * FROM dbo.gettitles(10)
> GO
>
> --
> HTH

> Kalen Delaney, SQL Server MVPwww.InsideSQLServer.comhttp://sqlblog.com

>
> "Alex Kuznetsov" <alk...@gmail.com> wrote in message
>
> news:1191519954.0...@k79g2000hse.googlegroups.com...
>
> > On Oct 4, 10:18 am, "Russell Fields" <russellfie...@nomail.com> wrote:
> >> Liz,
>
> >> UDFs are (alas) unable to UPDATE. They are read-only functions.
>
> > Russell,
>
> > if UDFs were able to update, that would lead to a lot of unpleasant
> > side effects, so it is better that UDFs are read only.

that's an intersting example, thanks, but I meant something different.
You can update against an inline UDF which is a parametereised view,
just as you can update an updateable view, but you can't have
modifications _inside_ UDF's body, just as you cannot have neither
"before select" nor "after select" triggers. The reason: allowing
modifications during selects can lead to nasty side effects when the
result of a select depends on its execution plan.

[Kalen Delaney]

Right, of course. If I had read more closely I would have realized that's
what you were talking about.
So you can forget this example.
;-)

--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com

"Alex Kuznetsov" <alk...@gmail.com> wrote in message

news:1191523347....@y42g2000hsy.googlegroups.com...

[sloan]

Here is a simple udf .. using with an update solution

Use pubs
go

if exists (select * from sysobjects where id =
object_id('udfEmployeeHasWorkedAtLeastXYears') and xtype = 'FN')

drop function dbo.udfEmployeeHasWorkedAtLeastXYears

GO

CREATE FUNCTION dbo.udfEmployeeHasWorkedAtLeastXYears( @years int ,
@hireDate datetime , @now datetime )

returns bit

as

BEGIN

declare @returnValue bit

select @returnValue = 0

if datediff ( m , @hireDate, @now ) > (@years * 12)

begin

select @returnValue = 1

end

return @returnValue

END

GO

declare @TenYears int

select @TenYears = 10

declare @holder table ( EmployeeID varchar(24) , HireDate datetime ,
IsTenYearCandidate bit )

INSERT INTO @holder (EmployeeID , HireDate , IsTenYearCandidate ) --

select

emp_id , hire_date , 0 from dbo.employee

Update @holder

Set IsTenYearCandidate = dbo.udfEmployeeHasWorkedAtLeastXYears ( @TenYears ,
h.HireDate , getdate() )

From @holder h

select * from @holder

"Liz" <l...@tiredofspam.com> wrote in message
news:OXkG1fpB...@TK2MSFTNGP05.phx.gbl...
>