You are confusing a RETURN
value for an OUTPUT
parameter. A RETURN is an optional status code of type INT
. Declare another parameter as OUTPUT.
Meaning, this is invalid in the Stored Procedure:
return @phoneID
Instead, add @phoneID nvarchar(100) OUTPUT
to the parameter list and remove the DECLARE @PhoneID
:
CREATE PROCEDURE GetRepPhoneID
(
@Rep NVARCHAR(100),
@phoneID NVARCHAR(100) OUTPUT
)
AS
SET NOCOUNT ON;
SELECT @phoneID = concat(CustomerRepPh, '~', RepID)
FROM Reps
WHERE CustomerRep = @Rep;
The above represents the entire proc. You don't need the RETURN
or the SET
.
Then in the C# code, you need to change how that parameter is specified:
SqlParameter ReturnParam = new SqlParameter("phoneID", SqlDbType.NVarChar, 100);
ReturnParam.Direction = ParameterDirection.Output;
Then remove this line as it is not needed since the value of the parameter will remain after the connection is closed:
string PhoneAndID = cmd.Parameters[1].Value.ToString();
And change the return
to be:
return ReturnParam.Value.ToString();
Lastly, you probably need to update the declaration of the input param as follows:
SqlParameter CustomerParam = new SqlParameter("Rep", SqlDbType.NVarChar, 100);
CustomerParam.Value = Rep;
CustomerParam.Direction = ParameterDirection.Input;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…