Charles Baldo
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect thanks a ton.
ASKER
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.
so when you include it within the select as in your procedure there is no last statement and assigns 0 to the return value.