Setting of @@FETCH_STATUS
in [Programming SQL]
|
Prev: Insert query using date as where condition
Next: TSQL Challenge 18 - Generate text formatted month calendars
From: Terry Steyaert on 30 Nov 2009 14:11 I have a snippet of code from a trigger: DECLARE @Saved_ID int SET @Saved_ID = (SELECT max(ID) FROM Table WHERE Name = @UserName) IF @@FETCH_STATUS = 0 AND @Saved_ID IS NOT NULL UPDATE ELSE INSERT Turns out my code ALWAYS runs the INSERT. If I add a temporary variable: DECLARE @Saved_ID int DECLARE @SavedStatus int SET @Saved_ID = (SELECT max(ID) FROM Table WHERE Name = @UserName) SET @SaveStatus = @@FETCH_STATUS IF @SavedStatus = 0 AND @Saved_ID IS NOT NULL This code works.... The only thing I can figure is the "IS NOT NULL" can re-set @@FETCH_STATUS. Am I missing something? (This is the only code changed in the trigger, and before the modification the trigger always insertted, now it updates when expected.) Is there a comprehensive list somewhere that lists when @@FETCH_STATUS gets modified? I've done some searches and haven't found anything. In fact, the MSDN page on @@FETCH_STATUS seems to imply that my select won't set it. Thanks in advance, Terry Steyaert
From: Michael Coles on 30 Nov 2009 14:35 Do you have a more complete snippet that shows the cursor being created and the @SaveStatus variable being declared, etc.? "Terry Steyaert" <TerrySteyaert(a)discussions.microsoft.com> wrote in message news:622D3964-5A9F-4474-8A70-D5D6CAEBCE11(a)microsoft.com... >I have a snippet of code from a trigger: > > DECLARE @Saved_ID int > > SET @Saved_ID = (SELECT max(ID) FROM Table > WHERE Name = @UserName) > > IF @@FETCH_STATUS = 0 AND @Saved_ID IS NOT NULL > UPDATE > ELSE > INSERT > > > Turns out my code ALWAYS runs the INSERT. If I add a temporary variable: > > DECLARE @Saved_ID int > DECLARE @SavedStatus int > > SET @Saved_ID = (SELECT max(ID) FROM Table > WHERE Name = @UserName) > > SET @SaveStatus = @@FETCH_STATUS > > IF @SavedStatus = 0 AND @Saved_ID IS NOT NULL > > > This code works.... The only thing I can figure is the "IS NOT NULL" can > re-set @@FETCH_STATUS. Am I missing something? (This is the only code > changed in the trigger, and before the modification the trigger always > insertted, now it updates when expected.) > > Is there a comprehensive list somewhere that lists when @@FETCH_STATUS > gets > modified? I've done some searches and haven't found anything. In fact, > the > MSDN page on @@FETCH_STATUS seems to imply that my select won't set it. > > Thanks in advance, > > Terry Steyaert
From: Jeroen Mostert on 30 Nov 2009 14:39 Terry Steyaert wrote: > I have a snippet of code from a trigger: > > DECLARE @Saved_ID int > > SET @Saved_ID = (SELECT max(ID) FROM Table > WHERE Name = @UserName) > > IF @@FETCH_STATUS = 0 AND @Saved_ID IS NOT NULL > UPDATE > ELSE > INSERT > I don't see any cursors. Why do you believe @@FETCH_STATUS should be useful to you at all? Are you confusing it with @@ROWCOUNT? In any case, it's much, much better not to think about how variables like @@FETCH_STATUS, @@ROWCOUNT and @@ERROR can be reset, but to simply assume they're impossibly volatile (as they basically are). That is, if you want to do anything with any of them other than testing them on their own, secure them immediately through a separate variable assignment and only then use them. The other way lies madness. -- J.
From: Bob Barrows on 30 Nov 2009 14:42
Terry Steyaert wrote: > I have a snippet of code from a trigger: > > DECLARE @Saved_ID int > > SET @Saved_ID = (SELECT max(ID) FROM Table > WHERE Name = @UserName) > > IF @@FETCH_STATUS = 0 AND @Saved_ID IS NOT NULL > UPDATE > ELSE > INSERT > > @@FETCH_STATUS is only relevant when using cursors: it specifically shows the result of a FETCH statement (FETCH NEXT, etc.). A SELECT statement is not a cursor. Checking @Saved_ID for a value should suffice. -- HTH, Bob Barrows |