| Author |
Topic |
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
 Posted - 2002-07-23 : 10:54:10
|
| Can someone please help me out here?I'm trying to make a user defined function that has an update statement in it. This code returns an error (Error 443: invaild use of update within a function):CREATE FUNCTION fnID()RETURNS numeric (9,0) AS BEGIN update tblIDset ID = ID + 1Declare @ID as numeric(9,0)select @ID = ID from tblIDreturn(@ID)ENDSQL SERVER 2000 STANDARD SP2 on WINNT4 SERVERDanielSQL Server DBAEdited by - sqlserverdba_dan on 07/23/2002 11:00:46 |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-23 : 11:19:31
|
Bol quote quote:
UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
Too the best of my knowledge (which is known to be off at the best of times) Updates and inserts cannot be run in a user-defined function ***except on a table var***-----------------------Take my advice, I dare ya |
 |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-07-23 : 11:24:09
|
quote:
Bol quote quote:
UPDATE, INSERT, and DELETE statements modifying table variables that are local to the function.
Too the best of my knowledge (which is known to be off at the best of times) Updates and inserts cannot be run in a user-defined function ***except on a table var***-----------------------Take my advice, I dare ya
Microsoft is gay. I dont think there is any need for a user defined function if it is so restricted that it cannot even do a simple update on a table.DanielSQL Server DBA |
|
|
|
sterobhun
Starting Member
12 Posts |
Posted - 2002-07-23 : 11:28:03
|
| I've never used SQL Server 2000, so I don't know what I'm talking about here. But I wonder, from the function can you call a stored procedure that would do the update? |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-23 : 11:36:24
|
| XP's yes... Sp's no-----------------------Take my advice, I dare ya |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-07-23 : 11:42:49
|
quote:
XP's yes... Sp's no-----------------------Take my advice, I dare ya
Can I write an XP with VB? I think I tried to do so before but ended up not being able to link the dll or something...DanielSQL Server DBA |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-23 : 11:45:34
|
| Hmm, outa my realm there... Never wrote an xp before. So my answer is.. umm.. wait for someone who knows what their talking about decides to post here-----------------------Take my advice, I dare ya |
|
|
|
royv
Constraint Violating Yak Guru
455 Posts |
Posted - 2002-07-23 : 11:56:13
|
| Writing an XP in VB would not be easy. There are libraries that would need to be translated for VB to call, and that by itself would be extremely difficult. These libraries are already translated in C++, which is why all XPs are written in C++. I agree with SQLServerDBA_Dan, Microsoft can really be gay at times.*************************Someone done told you wrong!Edited by - royv on 07/23/2002 11:58:15 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-23 : 11:59:58
|
quote:
I dont think there is any need for a user defined function if it is so restricted that it cannot even do a simple update on a table
Sorry Dan, nothing personal, but that is a short-sighted, bordering on really dumb, statement. If you want to do an UPDATE, what do you need a function for? I get by really well using the UPDATE statement without any functions at all.The #1 purpose of a relational database is to preserve data integrity, PERIOD. Do you really want to use or write a function that could compromise your data, and NOT have SQL Server do anything to avoid or correct that? I can imagine that happening and then hear you complain "SQL Server screwed up my data when I used this function!"...I can't even see where you would use this as a function anyway. There's nothing I see that prevents you from writing this as a stored procedure instead:CREATE PROCEDURE fnID() AS BEGIN update tblID set ID = ID + 1 Declare @ID as numeric(9,0) select @ID = ID from tblID return(@ID) END Not to mention that the way it was originally written doesn't guarantee it will pull the last value, which is what I assume you want to return. So, in effect, you're arguing for the ability to write a function that can cause data integrity problems, and you didn't even write it correctly! BTW, is there only supposed to be only one row in this table? Thanks for (not) explaining that to us. What exactly are you trying to accomplish anyway?If you really can't get over this "limitation" of SQL Server, by all means, use Oracle, PostGreSQL, MySQL, whatever you want, but you're not accomplishing anything simply by complaining about it.Edited by - robvolk on 07/23/2002 12:02:12 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-23 : 12:03:50
|
quote:
Microsoft is gay.
By ‘gay’, I take it you mean that in the pejorative sense. I think you better check yourself, as this type of slander against a certain group isn’t appropriate, isn't very mature or professional, and really should not be part of any discussion about SQL.<O>Edited by - Page47 on 07/23/2002 12:06:01 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-23 : 12:14:12
|
quote:
Microsoft is gay.
So was Alan Turing, the father of modern computer theory:http://www.turing.org.uk/turing/You could make a very strong argument that were it not for Turing's breakthroughs in computer design, the British codebreaking effort during WW II would not have succeeded as well as it did, and the war could have gone on much longer, and perhaps the Allies could have lost...but thanks to a gay man, that didn't happen.  I don't think Microsoft would consider a comparison to Alan Turing to be a slander against them. Alan, on the other hand, probably wouldn't appreciate being compared to Microsoft... |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-23 : 12:17:55
|
| Bleh, with where this thread is going I might as well addAll Your Base Are Belong To Us-----------------------Take my advice, I dare yaEdited by - M.E. on 07/23/2002 12:18:13 |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-07-23 : 12:32:56
|
quote:
I don't think Microsoft would consider a comparison to Alan Turing to be a slander against them. Alan, on the other hand, probably wouldn't appreciate being compared to Microsoft...
Except, in this case, I don't think Dan was saying "Microsoft is a lot like others that are making breakthroughs in computer science". Rather he was using "gay" in exchange for "bad", and I think homosexuals probably don't appreciate being compared with "bad".<O>EDIT: quote:
Bleh, with where this thread is going I might as well add
Oh, I know, being mature is such a buzz-kill....Edited by - Page47 on 07/23/2002 12:34:10 |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-07-23 : 12:40:55
|
| Ok ok. I understand that I should have been more considerate for Microsoft in my post.Rob:The table only contains 1 record (The last record used). I'm not sure how this would sometimes not pull the correct number in that case. Also. I ended this topic and began a new one for what I needed. In this topic I simply needed to know why the update was not working.I do see a use for user defined functions but they are not really needed when I can just use a case statement of something of the like. Maybe I dont know the full potential of the UDF because I am mostly SQL 7 and have not used UDFs much. I think your responses are great and you have always helped people on this forum and always been right. But in this case I dont feel you are. Your response makes me think you have a lot of Microsoft stock... I mean dont get me wrong here I think SQL Server is the best DB on the market but there are some areas that need improvement.All I wanted to do was simply get a unique id for each record in a select statement. I dont care to tie the ID to anything at all so your idea of integrity is not an issue.You try to make me sound like I have no idea what I'm talking about but the fact is that I just didnt explain my situation enough and you jumped the gun with some crazy post. Thats just wrong. This topic should have been closed with my original post and a simple answer of "Sorry you cant do DML with a UDF". So I'm wrong for slamming Microsoft there but your comment was also uncalled for.DanielSQL Server DBA |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-07-23 : 12:43:02
|
quote:
quote:
I don't think Microsoft would consider a comparison to Alan Turing to be a slander against them. Alan, on the other hand, probably wouldn't appreciate being compared to Microsoft...
Except, in this case, I don't think Dan was saying "Microsoft is a lot like others that are making breakthroughs in computer science". Rather he was using "gay" in exchange for "bad", and I think homosexuals probably don't appreciate being compared with "bad".<O>EDIT: quote:
Bleh, with where this thread is going I might as well add
Oh, I know, being mature is such a buzz-kill....Edited by - Page47 on 07/23/2002 12:34:10
Yes. Sorry.. I need to keep in mind that this is an international forum and some people may not understand slang for my part of the world. It was uncalled for.DanielSQL Server DBA |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-07-23 : 12:55:49
|
| I didn't understand your slang. What did it mean? |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2002-07-23 : 12:57:25
|
| Functions called within a query should not have side-effects because you have no guarantee of when and how often they will be called in the execution of that query. |
|
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2002-07-23 : 13:10:46
|
quote:
Functions called within a query should not have side-effects because you have no guarantee of when and how often they will be called in the execution of that query.
I supose I can understand why you cannot issue an update in the function but in this case it would do no harm But if MS allowed updates for some cases and not for others or if they allowed updates for all then people could destroy their data.I dont know I guess I would rather have the ability to decide if I want to risk destroying my data or not. It shouldnt be in Microsofts hands to make that choice for me because it limits me from doing things that would have no impact on my data.DanielSQL Server DBA |
|
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-23 : 13:55:59
|
quote:
Oh, I know, being mature is such a buzz-kill....
This would also be correct-----------------------Take my advice, I dare yaEdited by - M.E. on 07/23/2002 13:58:09 |
|
|
|
Lana
Starting Member
6 Posts |
Posted - 2002-08-02 : 08:48:09
|
| I think this will guarantee you are "pulling a unique value" when you need to get the next counter valueDECLARE @id numeric(9,0)UPDATE tblID SET @id = id = id + 1 |
|
|
|
Clothahump
Starting Member
18 Posts |
Posted - 2002-08-02 : 09:47:15
|
| Bleh, with where this thread is going I might as well add All Your Base Are Belong To Us ----------------------- If you're going to post the mantra of the universe, you should alsopost references so that the uneducated can obtain enlightenment:http://www.planettribes.com/allyourbase/AYB2.swfhttp://www.maficdesign.com/ayb/mrt.swf |
|
|
|
Next Page
|
BAH!! UPDATE in Function doesnt work??