CODEWITHSUNDEEP
c#sqlstored-procedures
Casey ScriptFu Pharr
Casey ScriptFu Pharr

Reputation: 1670

Conversion for param and stored procedure not working

I am trying to start usign stored procedures. Well, I currently have a query that is just returning a single value that is a string. I can't seem to see what I am doing incorrectly here.

The sql is below:

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

Declare @ReturnVal Varchar(20)

-- Insert statements for procedure here
set @ReturnVal = (select a.AccBCPublicId from [MyTable Goes Here] a where a.AccId = @ACCId)

return @ReturnVal

END

using (SqlCommand sqlCommand = new SqlCommand("[dbo].[My Stored Procedure]", sqlConnection) { CommandType = CommandType.StoredProcedure })
  {
    //clean the starting 0's before sending. Originally had leading zeros
    sqlCommand.Parameters.Add(new SqlParameter("@ACCId", strAccId.TrimStart(Convert.ToChar("0"))));
    SqlParameter returnParam = new SqlParameter("@ReturnVal", SqlDbType.NVarChar, 20) {Direction = ParameterDirection.ReturnValue };
    sqlCommand.Parameters.Add(returnParam);

    sqlCommand.ExecuteNonQuery();   

    string bcAccount = (string)returnParam.Value;
 }

I keep getting the following error. I do see why it is seeing as int value type.

Conversion failed when converting the varchar value 'bc:99988' to data type int.

Upvotes: 1

Views: 107

Answers (1)

Steve
Steve

Reputation: 216293

A value passed with the RETURN T-SQL statement can only be an integer expression, you can't pass back a string. It seems that the simplest option is to use ExecuteScalar

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

select a.AccBCPublicId from [MyTable Goes Here] a where a.AccId = @ACCId

And in your code use

using (SqlCommand sqlCommand = new SqlCommand(.....))
{
    sqlCommand.Parameters.Add(.....)
    object result = sqlCommand.ExecuteScalar();
    if(result != null)
        string bcAccount = result.ToString();
    ....
}

Upvotes: 1

Related Questions