• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

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?
0
Charles Baldo
Asked:
Charles Baldo
  • 2
  • 2
1 Solution
 
appariCommented:
change your procedure to

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

Open in new window

0
 
appariCommented:
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.
0
 
Charles BaldoSoftware DeveloperAuthor Commented:
Perfect thanks a ton.
0
 
Charles BaldoSoftware DeveloperAuthor Commented:
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.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now