October 21, 2003 at 12:30 am
I disagree with the fact that the answer to this question cannot be , that the return values must be declared since if the procedure is changed in this way
create procedure myproc
@a_key int,
@a_field varchar(40),
@retval int Output
as
update mytable set myfield = @a_field where mykey = @a_key
if (@@error <> 0)
return -1
if @@rowcount = 0
return 0
return 1
And then called like this
exec myproc 1, 'first rec', @retval
The query would return the correct result.
So I am quite unhappy that I missed out on my four points today. I suppose there is always more than one solution to a problem.
October 21, 2003 at 1:59 am
... if the procedure is changed in this way
But it wasn't, and the question clearly stated return rather than output variables, and your proc would still not work because it has the same bug as the QOD one.
Good question but wayyy too easy for 4 points. Should have been 2
Keith Henry
DBA/Developer/BI Manager
Keith Henry
October 21, 2003 at 2:50 am
But the question was "What is the error in this situation ?" i.e. the sample code provided. Anyway, no value is assigned to @retval in your new proc so it would still cause the same problem
October 21, 2003 at 5:22 am
Actually the answer is better expressed in BOL
quote:
This variable is set to 0 by any statement that does not return rows, such as an IF statement.
If you however reverse the proc like so
CREATE PROCEDURE myproc
@a_key int,
@a_field varchar(40)
as
update mytable set myfield = @a_field where mykey = @a_key
if @@rowcount = 0
return 0
if @@error <> 0
return -1
return 1
GO
It will work becuase the @@rowcount test is first then @@error which is not reset by IF. So the answer is correct but the code is savageable by making a simple change. Many times errors in most procedural and object oriented languages order of operation can be very important such as is the case here.
October 21, 2003 at 8:06 am
I guess this QOD was closer to reality, where the simple problem gets buried under everything else. I did happen to see right through to the answer, but I suppose not everyone did.
Antares - I thought @@error also resets. Regardless, I think the best solution is this:
create procedure myproc
@a_key int,
@a_field varchar(40)
as
declare @rowcount as int, @error as int
update mytable set myfield = @a_field where mykey = @a_key
select @rowcount = @@rowcount, @error = @@error
if (@error <> 0)
return -1
if @rowcount = 0
return 0
return 1
Data: Easy to spill, hard to clean up!
October 21, 2003 at 8:30 am
Fortunately it does not stubob. From SQL BOL
quote:
@@ERROR returns the number of the error message until another Transact-SQL statement is executed.
October 22, 2003 at 1:25 am
Actually the IF does reset @@error. If you've coded assuming it does not, you may want to revisit that sql.
The test :
raiserror('an error happened',16,1)
select @@error
result
------
Server: Msg 50000, Level 16, State 1, Line 1
an error happened
-----------
50000
Then try:
raiserror('an error happened',16,1)
if @@error = 0 print 'no error here'
select @@error
result
------
Server: Msg 50000, Level 16, State 1, Line 1
an error happened
-----------
0
once again reversing IF logic:
raiserror('an error happened',16,1)
if @@error != 0 print 'yep an error here'
select @@error
result
------
Server: Msg 50000, Level 16, State 1, Line 1
an error happened
yep an error here
-----------
0
Ian Dundas
Senior IT Analyst - Database
Manitoba Public Insurance Corp.
October 22, 2003 at 1:57 am
quote:
If you however reverse the proc like soblah blah blah
if @@rowcount = 0
return 0
if @@error <> 0
return -1
return 1
It will work becuase the @@rowcount test is first then @@error which is not reset by IF.
This doesn't work because the @@error now refers to "if @@rowcount = 0", however even if it did wouldn't it change the values returned by the function? I mean the original QOD was trying to return -1 when an error occured and 0 if no error but no rows. This one would return (if it worked) 0 if no rows occured (which will probably happen if an error occurs too) and -1 if rows returned but an error.
Keith Henry
DBA/Developer/BI Manager
Keith Henry
October 22, 2003 at 6:06 am
Well I do agree that IF reset @@error since I test without the SP (which should never throw an error that would be trappable in most cases after reviewing).
However to get both @@rowcount and @@error you need to do
SELECT var1 = @@rowcount, var2 = @@error
because seperately they reset.
October 22, 2003 at 7:04 am
I only have a smll complaint, but that's cuz I'm not a guru yet =).
It would have been nice if we were told what the problem was that the programmer was having. In a real world situation, you would have gotten at least that much from a programmer...you would hope anyway.
I got it wrong, but would have still made a samll complaint if I got it right.
October 23, 2003 at 2:11 am
quote:
In a real world situation, you would have gotten at least that much from a programmer...you would hope anyway.
In my dreams. Useually it goes:
quote:
It Ddesn't workWhat Error are you getting?
It just doesn't work when I click on the button.
Which button?
Just f***in* fix it will you (hangs up)
Keith Henry
DBA/Developer/BI Manager
Keith Henry
October 23, 2003 at 5:58 am
quote:
In my dreams. Useually it goes:quote:
It Ddesn't workWhat Error are you getting?
It just doesn't work when I click on the button.
Which button?
Just f***in* fix it will you (hangs up)
Keith Henry
DBA/Developer/BI Manager
Sounds like an end user response not a programmer. =)
October 23, 2003 at 8:10 am
quote:
Sounds like an end user response not a programmer. =)
In that case it goes
quote:
It doesn't workWhat Error are you getting?
It just doesn't work when I do rst equals cmd dot execute
That's very helpful, what are you trying to execute
It's a stored procedure, Just f***in* fix it will you (hangs up)
Keith Henry
DBA/Developer/BI Manager
Keith Henry
October 23, 2003 at 8:52 am
It's true. Speaking as someone who is DBA, programmer, and sometimes end user, I do get to curse at myself a lot...
Data: Easy to spill, hard to clean up!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply