"Cliff" <cl***@walkacro ssfire.com> wrote in message
news:54******** *************** ***@posting.goo gle.com...
here's my stored procedure:
CREATE PROCEDURE proc
@id varchar(50),@ps wd varchar(20),@no _go int OUTPUT
AS
SET NOCOUNT ON
SELECT user_id FROM profile
WHERE user_id=\@id AND pswd=\@pswd
IF \@\@ROWCOUNT = 0
BEGIN
SET \@no_go = 1
END
ELSE
BEGIN
SELECT date,date_mod FROM ans
WHERE user_id=\@id
SET \@no_go = 0
END
Using the PERL odbc_more_resul ts function I can retrieve the data in
the second select statement whether the rowcount is 0 or not. Any
suggestions how to stop this
Looking at one of your previous posts (the one Erland replied to), I notice
that you've simplified the above code from what you previously posted.
Specifically, do you still have these lines in your real code?
SELECT user_id FROM myTable
WHERE user_id=@id AND iyt=@iyt
SET @id_err = @@ERROR
IF @@ROWCOUNT <> 0
BEGIN
The SET @id_err = @@ERROR statement increments @@ROWCOUNT to 1, so the
following IF statement is always false, and execution always passes to the
second SELECT.
Since @@ROWCOUNT is volatile in the same way as @@ERROR, a common approach
is this:
SELECT user_id FROM myTable
WHERE user_id=@id AND iyt=@iyt
select @err = @@ERROR, @rows = @@ROWCOUNT
In this way, both values are captured reliably. If this is not the
explanation, then I can suggest two other things. First, use Profiler to
view the SQL being submitted to the server by your Perl script, to ensure
that it's really doing what you think it is. Second, use the stored proc
debugger to trace execution of the proc and watch the value of @@ROWCOUNT.
If you still have an issue after that, perhaps you could post the results of
those tests.
Simon