July 11, 2007 at 12:06 pm
Hi Dudes,
I have a requirement to write a function to update a base table ,with the below code i get the following error message
server Msg 443,level 16, State2,Procedure Fn_Update,Line 9
Invalid use of 'UPDATE' Within a function.
Create Function Fn_update (@A Varchar(10),@B varchar(2))
Returns Varchar(2)
As
Begin
Declare @count Varchar(1)
Select @Count=Count(*) from dbo.UserTable Where A=@A
If @Count > 0
Begin
Update dbo.UserTable Set B=@B Where A=@A
END
Return @Count
End
I read it is not possible to do any DML statements in function ,then how to achieve the requirement.
July 11, 2007 at 1:31 pm
you have to use an stored procedure. SQL Server Functions are not meant for DML.
* Noel
July 11, 2007 at 4:48 pm
You don't need all this hassle at all.
Update dbo.UserTable
Set B=@B
Where A=@A
That's it.
If there are no rows with A=@A no rows gonna be updated anyway.
And, yes, get rid of the cursor. You can update all relevant rows in the table with a single update statement.
_____________
Code for TallyGenerator
July 13, 2007 at 11:37 am
Thanks dudes..but i have a requirement from Dev team to provide a function to pass in application.what are the possibilities to apply in function.
July 13, 2007 at 12:20 pm
You can't do an update in a function.
July 13, 2007 at 12:43 pm
Listen to noeld. A function, by definition, does not directly make changes to your database -- that is what procedures are for. This is not a SQL Server rule, or even a SQL rule. Any modern programming language demands that the "state" of the application should not be changed from within a function.
Your Dev team may require you to write a function that brings them a cup of fresh coffee when they start getting tired. As highly desired as that may be, the only response is "too bad!"
The "create function" code you wrote can be changed to "create procedure" with little change. If you want to return the final count, use an OUT parameter. You'll get what you want without having to defeat established safeguards written into the language.
If you get any flack from Dev, send them to me. 😉
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply