Link to home
Start Free TrialLog in
Avatar of Charles Baldo
Charles BaldoFlag for United States of America

asked on

Stored Procedure does not return value

Hello, I have the following stored procedure

CREATE PROCEDURE dbo.getLogin
(
      @OperatorID INT,
      @OperatorPassword varchar(12),
      @CompanyID INT OUTPUT,
                @RecordExists INT OUTPUT
)
AS
SELECT
   @RecordExists = @@ROWCOUNT,
   @CompanyID = CompanyID
FROM
   dbo.Operators
WHERE
   OperatorID = @OperatorID AND
   OperatorPassword = @OperatorPassword


When I execute this

DECLARE @CID INT
DECLARE @RE INT

EXEC getLogin
      @OperatorPassword = 'test',
      @CompanyID = @CID OUT,
      @RecordExists = @RE OUT,
      @OperatorID = 3417

PRINT @CID
PRINT @RE

I get
3
0

3 is the company code bt zer0 is wrong because obvious the record exists and @@ROWCOUNT = 1.     I also get this in my c# code that executed the procedure

When I run this query
SELECT
   @@ROWCOUNT,
   CompanyID
FROM
   dbo.Operators
WHERE
   OperatorID = 3417 AND
   OperatorPassword = 'test'  

I get the right values 1 and 3

Any ideas why the stored procedure does not return the @@ROWCOUNT?
ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the reason @@rowcount returns the number of rows affected by the last statement.
so when you include it within the select as in your procedure there is no last statement and assigns 0 to the return value.
Avatar of Charles Baldo

ASKER

Perfect thanks a ton.
Thanks again for the followup. I am just past novice level on this stuff so the more I understand the better. I appreciate your effort.