For Update of Cursor in a UDF

Hi,
I am writing a UDF that returns a table variable. In the UDF, I have a
cursor that I want to update. I am getting a syntax error on the UPDATE.
Is there a reason I cannot do this is a user defined function?
Thanks
SteveYou cannot update data inside of a UDF. That restriction is in place,
AFAIK, to avoid some logic problems that might occur, e.g., if a scalar UDF
is being called row-by-row and updates rows that have already been
processed.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:3FE89A39-AB3F-4464-BBB3-ED652EB80D0A@.microsoft.com...
> Hi,
> I am writing a UDF that returns a table variable. In the UDF, I have a
> cursor that I want to update. I am getting a syntax error on the UPDATE.
> Is there a reason I cannot do this is a user defined function?
> Thanks
> Steve|||>> I am writing a UDF that returns a table variable. In the UDF, I have a
You cannot do any updates which changes the persisted data or the database
state from within a UDF. This is by design and documented in SQL Server
Books Online.
Perhaps if you post your overall requirements with relevant information,
others might suggest an alternative. Using a cursor inside a table-valued
UDF for updating certain data seems a very convoluted route.
Anith|||Why are you calling a UDF from a cursor? And are you sure you need to
use a cursor at all?
Please post DDL, sample data and explain your required end result if
you need more help.
David Portas
SQL Server MVP
--|||Thanks for all the responses, obviously, my approach was not too popular.
I like baseing ACCESS reports off of UDF table variables. For this
particular report, I need to process a lot of data and it required me to use
a cursor, and I wanted to update a column so the next pass through would kno
w
I had been there. If my UDF, I gather all this information and Insert it
into the table variable, then that is returned to ACCESS.
It is nice doing it with a UDF cause of the table variable. If I use a
Stored Proc, I would have to CREATE a temp table in the Proc and populate it
,
then base the report on the temp table, if it is still around.
Steve
"Anith Sen" wrote:

> You cannot do any updates which changes the persisted data or the database
> state from within a UDF. This is by design and documented in SQL Server
> Books Online.
> Perhaps if you post your overall requirements with relevant information,
> others might suggest an alternative. Using a cursor inside a table-valued
> UDF for updating certain data seems a very convoluted route.
> --
> Anith
>
>|||I think he's calling a cursor from a UDF :)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124296660.434609.202840@.g14g2000cwa.googlegroups.com...
> Why are you calling a UDF from a cursor? And are you sure you need to
> use a cursor at all?
> Please post DDL, sample data and explain your required end result if
> you need more help.
> --
> David Portas
> SQL Server MVP
> --
>|||>> For this particular report, I need to process a lot of data and it
Cursors are seldom required for data updates. In most cases, you'd write a
single UPDATE statement, preferably within a stored procedure to do any
updates, but it depends on what you exactly meant by "process"
If you are interested in getting some additional assistance, please go
through www.aspfaq.com/5006 and post relevant information for others to
better understand your problem scenario.
Anith|||Thanks for the information. I don't update via a Cursor that often, but in
this case, it was sitting on the row I wanted update, and just thought it
would be convienient. Either way though, if I can't do any updates in a UDF
,
I am taking the wrong approach.
Thanks
"Anith Sen" wrote:

> Cursors are seldom required for data updates. In most cases, you'd write a
> single UPDATE statement, preferably within a stored procedure to do any
> updates, but it depends on what you exactly meant by "process"
> If you are interested in getting some additional assistance, please go
> through www.aspfaq.com/5006 and post relevant information for others to
> better understand your problem scenario.
> --
> Anith
>
>