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
>
>

FOR UPDATE clause in cursor

I'm refactoring a cursor written by another employee. It includes a cursor
that uses the FOR UPDATE clause to update records as the cursor iterates
through the records. Please assume that the cursor cannot/should not be
replaced.
When executing the snippet of code below, I get the SQL Server error below.
What syntax would allow me to make the cursor read/write rather than read
only? I tried making the cursor DYNAMIC, but this did not appear to help.
Thanks in adavnce.
-Mark
Server: Msg 16957, Level 16, State 4, Procedure p_om_daily_reload, Line 402
FOR UPDATE cannot be specified on a READ ONLY cursor.
********************** SNIPPET OF CODE ****************************
declare @.audit_note varchar(200)
declare reg_curs_mentorhip_cancelled CURSOR DYNAMIC
FOR
-- Look for existing payment records using the reg_num that have been
marked as canceled in informix.
SELECT DISTINCT mp.mentorship_id, im.hours
FROM t_om_informix_mentorship im
INNER JOIN t_om_app_mentorship_payment mp
ON im.reg_num = mp.reg_num
WHERE im.reg_status = 'C'
FOR UPDATE -- MARK: Added to allow cursor data to be updated. Default is
read-only
OPEN reg_curs_mentorhip_cancelled
FETCH NEXT FROM reg_curs_mentorhip_cancelled INTO @.mentorship_id,
@.add_purchase
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
IF (@.@.FETCH_STATUS <> -2)
BEGIN
PRINT'* 4a , subtract the amount of time refunded'
UPDATE t_om_app_mentorship
SET time_purchased = time_purchased - @.add_purchase
WHERE mentorship_id = @.mentorship_id;
PRINT'* 4a , remove payment record'
DELETE t_om_app_mentorship_payment
WHERE CURRENT OF reg_curs_mentorhip_cancelled
PRINT'* 4a ,add audit trail'
SET @.audit_note = 'mentorship_id = ' + convert(varchar(15),
@.mentorship_id) + ', Hours affected: ' + CAST(@.add_purchase as varchar)
EXEC p_om_data_audit_trail_i @.user_key = 'unknown', @.priority = 1,
@.create_user = 'Reload_Script', @.category = 'Payments',
@.issue = 'Mentorship Payment either disappeared or was cancelled',
@.note = @.audit_note
END
FETCH NEXT FROM reg_curs_mentorhip_cancelled INTO @.mentorship_id,
@.add_purchase
END
CLOSE reg_curs_mentorhip_cancelled
DEALLOCATE reg_curs_mentorhip_cancelledThe cursor cannot be updateable because of the DISTINCT modifier in the
query.

> Please assume that the cursor cannot/should not be
> replaced.
No chance of me assuming that! Cursors bad. Updateable cursors worse.
;-)
David Portas
SQL Server MVP
--|||>> Please assume that the cursor cannot/should not be replaced. <<
Is this a teaching exercise of some kind? "Look Igor, screwdriver
better than rocks for cabinet making!!" "Igor not like screwdriver!
Igor have rock!"
Based on ~20 years with SQL, ten years on the ANSI Standards committee
of SQL, and six books on the language, I find Kosher Pork easier to
assume -- Hey, we got genetic engineering and can fix those feet!
I have written five cursors in my career and I know that at least three
of them could have been done with a CASE expression. They would have
run **orders of magnitude** faster and been easierr to maintain.
Also, when you post code could you consistently uppercase reserved
words? Learn about semi-colons in SQL? ISO_11179 rules? Not use PRINT
in application code (it is for debugging only)? What about the basic
rule that audit trails are external to the procedures, so you know that
they valid?
You have code like an OO or COBOL program. The basic problem; this is
procedural code written in SQL, a declarative language. Your entire
approach and vocabulary are non-relational (Records in SQL').
Dave was nicer than me (who isn't?). EVERYTHING needs to be re-done
whiel there is still time. This is so crappy it is not worh saving and
might have screwed up data integrity already.

for loop

HeaderLoop: for forHeader as curHeader dynamic scroll cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 do
set AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(AcctNum);
set Cntr=Cntr+1
end for;

The above is the sybase version of a 'for loop' . The query
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 results in 1000 results.
How do I change that in SQL?? Do we have a for loop ??
I checked in BOL but it is confusing with "browse" etc n some other options.

can I write like this?

for { Browse { declare curHeader dynamic cursor for
select lngALSHeadrID from "DBA".ALSHEADR where lngFedTaxID>0 }
set @.AcctNum=lngALSHeadrID;
exec "DBA".sp_ALSHeadr2Policy(@.AcctNum);
set @.Cntr=@.Cntr+1
}


I duno its just my guess, can any one help me out. @.Cntr and @.Acctnum are declared
in the beginnning.

tksDECLARE @.cntr int
SELECT @.cntr=0
WHILE @.cntr < 1000
BEGIN
SELECT @.cntr = @.cntr + 1
-- Other statements
END
SELECT @.cntr|||Brett

thanks for the help, look at wut I did. first thing it is not sure that
it is 1000 records. So I counted total num of records and did looping
with that count. But the problem is at the select stmt (in the loop)
the stmt gives 1000 (assume) records, thus 1000 values to the
the variable lngAlsHeadrID, where in the loop we are taking each
value and then setting it to the variable @.AcctNum. Hope u got my ques.
My answer should be like this, I should take each value of
lngAlsHeadrID set it to @.Acctnum and then pass it thru sproc and then
take the second value n so on.......

set @.RowCount = select count(lngALSHeadrID) from ALSHEADR where
lngFedTaxID>0 and lngFedTaxID<999999999 }

WHILE @.cntr < @.RowCount1
BEGIN
declare curHeader cursor dynamic for
select lngALSHeadrID from ALSHEADR where lngFedTaxID>0
set @.AcctNum = lngAlsHeadrID
Exec ALSHeadr2Policy(@.AcctNum)
SELECT @.cntr = @.cntr + 1
END
SELECT @.cntr

Can you suggest me??

Thanks alot|||Just to be picky, I would substitute SET for SELECT in Brett's code, 'cause MS says it's better to, and I do everything they say 8-}|||How big is ALSHeadr2Policy?

Can you post it?

I'd rather give you a set based solution....|||Does smell like a join waiting to happen, doesn't it?|||Originally posted by bpdWork
Just to be picky, I would substitute SET for SELECT in Brett's code, 'cause MS says it's better to, and I do everything they say 8-}

I seen huge global wars about that...WHERE specifically did yo see that?

And what would you prefer

SET @.a = 1
SET @.b = 1
SET @.c = 1
SET @.d = 1
SET @.e = 1
SET @.f = 1

Or

SELECT @.a = 1, @.b = 1, @.c = 1, @.d = 1, @.e = 1, @.f = 1

Wouldn't you think 1 op is better than 6?|||Brett

Thats too big. It has several for loops, while loops and all.
If I can get the idea of syntax for this for loop then I can try to
solve that sproc. It is around 500 lines, just donn wann to confuse you.

Any more suggestions for the above ques.

Thanks|||I got it from BOL under SELECT @.local variables:
"It is recommended that SET @.local_variable be used for variable assignment rather than SELECT @.local_variable. For more information, see SET @.local_variable."

Actually, I think it ends up being 6 ops anyway. I guess it depends on whether you are in the "1 operation per line" camp or not.

I fall somewhere in between: One declare statement, new line for each variable.|||Just out of curiousity, could you pst one of the loops from the big sp?

-b|||Wait a minute, but isn't kir441 talking about a cursor? Check BOL for example of cursor declaration and usage.|||Rdja

Even I got the same idea. Whether I can use a cursor instead of that loop.??

bdp: Here is the for loop

for forIns as curIns dynamic scroll cursor for
select decAccountNum as AccountNum,strInsType,intTypeCode,strVendCode,
datLoanEnds,intTerm,curPremium,strPremType,curFace Amt,strPolicyNum, datStarted,datPlaced,datCanceled,strInsID,strAutoC ancel,datAutoCanceled
from "DBA".ALSINSUR where ALSINSUR.decAccountNum=AcctNum do
set PolicyNum=decAccountNum||'-'||strInsType||'-'||intTypeCode;
set Premium=curPremium;
---------- Make sure we have a product
set ProductID=null;
select MIN(lngProductID) into ProductID from "dba".Products
where strShortCode=strInsType and strVendorCode=strVendCode and lngVendorID=18; %% FTB ALS System
if ProductID=null or ABS(0||ProductID)<1 then
--Add Product to Product Table (ADD LATER)
call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR','Ne ed to Add'
||strInsType||'-'||strVendCode||' to the Products tables.')
end if
;
if ProductID=null or ABS(0||ProductID)<1 then -- Policy will not be added
call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR','Il legal ProductID '
||strInsType||'-'||strVendCode||' for PolicyNum='||PolicyNum)
else
-- We have a Person, Customer and Product that are setup correctly
set PolicyID=null;
select MIN(lngPolicyID) into PolicyID from "DBA".Policies
where strPolicyNumber=PolicyNum and lngProductID=ProductID;
set bForced=0;
set PayModeID=8; -- Single Payment
set PayMethID=3; -- Included in Loan Amout
if strInsType='NIU' then -- Loans that premiums funded as part of the loan
set bForced=1
end if
;
set RefEmpID=null;
set BranchID=null;
set AgentID=null; -- put in default AgentID of Default Record
select MIN(lngEmployeeID) into RefEmpID from "DBA".Employees where lngFTBEmpID=lngOfficerID;
select MIN(lngBranchID) into BranchID from "DBA".Branches where lngRespCntr=intRespCenter;
if PolicyID=null or ABS(0||PolicyID)<1 then -- Need to add Policy
insert into "DBA".Policies(lngBranchID,lngReferEmpID,lngCustomerID, lngPmntModeID,
lngPmntMethID,lngProductID,strPolicyNumber,strStat us,datOrigEffective,
datEffective,datRenewal,curPremium,lngAgentID,blnF orced) values(
BranchID,RefEmpID,CustID,PayModeID,PayMethID,Produ ctID,PolicyNum,'IF',
datStarted,datPlaced,datExpDate,Premium,AgentID,bF orced);
if sqlstate<>'00000' then
call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','ERROR','Ca nnot Add Policy Record for PolicyNum='
||PolicyNum||' Loan # '||AcctNum) --else call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','TEST','Add ed PolicyNum='||PolicyNum)
end if
else -- Update the current Policy
update "DBA".Policies set lngBranchID=BranchID,lngReferEmpID=RefEmpID,
lngCustomerID=CustID,lngPmntModeID=PayModeID,lngPm ntMethID=PayMethID,
lngProductID=ProductID,strPolicyNumber=PolicyNum,s trStatus='IF',
datOrigEffective=datStarted,datEffective=datPlaced ,datRenewal=datExpDate,
curPremium=Premium,lngAgentID=AgentID,blnForced=bF orced,datLastStamp=current timestamp
where lngPolicyID=PolicyID and lngProductID=ProductID --call "DBA".sp_LogMsg('SQLA','sp_ALSHeadr2Policy','TEST','Upd ated PolicyNum='||PolicyNum||' ProductID:'||ProductID)
end if
end if -- ProductID = Null
end for -- Insurances
end if -- CustID
end if -- PersonID
end for; -- ALSHeadr's

ignore those sybase commands as we can change that stuff.
but this is one of the for loops in the sproc.

Any suggestions.

Tks|||declare @.a int
declare @.a1 int
declare @.a2 int
declare @.a3 int
declare @.a4 int
declare @.a5 int
declare @.a6 int
declare @.cntr int

set @.cntr=0

while @.cntr < 100000
begin
set @.a = @.cntr
set @.a1 = @.cntr
set @.a2 = @.cntr
set @.a3 = @.cntr
set @.a4 = @.cntr
set @.a5 = @.cntr
set @.a6 = @.cntr
set @.cntr = @.cntr+1
end

EXECUTION DURATION : 1.28 seconds

--

declare @.a int
declare @.a1 int
declare @.a2 int
declare @.a3 int
declare @.a4 int
declare @.a5 int
declare @.a6 int
declare @.cntr int

set @.cntr=0

while @.cntr < 100000
begin
select @.a = @.cntr, @.a1 = @.cntr, @.a2 = @.cntr, @.a3 = @.cntr, @.a4 = @.cntr, @.a5 = @.cntr, @.a6 = @.cntr, @.cntr = @.cntr+1
end

EXECUTION DURATION: 0.33 seconds|||Try This

declare @.i int
select @.i = id from sysobjects)
select @.i
go
declare @.i int
set @.i = (select id from sysobjects)
select @.i
go

SELECT @.local_variable is usually used to return a single value into the variable. It can return multiple values if, for example, expression is the name of a column. If the SELECT statement returns more than one value, the variable is assigned the last value returned.

If the SELECT statement returns no rows, the variable retains its present value. If expression is a scalar subquery that returns no value, the variable is set to NULL.

Use the SET statement to assign a value that is not NULL to a declared variable. The SET statement that assigns a value to the variable returns a single value.

BTW I prefer
SELECT @.local_variable|||Yeah me too...but I confirmed that the SET execution is faster (for 1000 iterations) by about 4000 milliseconds...

careful...don't blink...|||Oh, and btw your code is either Oracle or udb...isn't it...

||, or the concat function isn't available in SQL Server...they use +

And your reference to CALL makes me think it's UDB|||I'm not that picky, and I'm not trying to start something here. I think performace-wise, there's probably very little difference (a few blips here or there).

I use SET when I am assigning a fixed value (SET @.x = 5) and SELECT if I am querying for a value (SELECT @.x = mynum FROM mytable).

My rule is just a personal thing, so my code is more readable to me.|||Originally posted by bpdWork
SELECT if I am querying for a value (SELECT @.x = mynum FROM mytable).


Well Don't you have to?

USE Northwind
GO

DECLARE @.x varchar(10)

SELECT TOP 1 @.x = CustomerID FROM Orders

SELECT @.x
GO

DECLARE @.x varchar(10)

SET TOP 1 @.x = CustomerID FROM Orders

SELECT @.x
GO|||For SELECT, yes, but I don't *NEED* to use SET, I just do.|||Weren't we taling about for loops?

for deleting duplicate rows subquery or cueser is Better in performance?

For deleting duplicate rows, i can use cursor and subquery.

cursor code

Declare dup_cursor cursor for
select acctnumber from LinkUrnABSADMBAR
group by acctnumber
having count(*) > 1

Open dup_cursor

Fetch Next from dup_cursor INTO @.acctnumber

While (@.@.Fetch_Status = 0)
Begin

Delete from LinkUrnABSADMBAR
where acctnumber = @.acctnumber

Fetch Next from dup_cursor INTO @.acctnumber
End

Close dup_cursor
Deallocate dup_cursor

Subquery code

delete from galupload2000..test where id in (select id from galupload2000..test group by id having count(*) >1)

My question is which one is Better in performance?

Thanks

Sandipan

Subqueries run faster than cursors as a general rule.

BobP

|||Thanks Bob