Varchar value not passing into SQL Server stored procedure

Question

Following is my code to call my stored procedure:

SqlCommand commandSql = new SqlCommand("GetbyProgramID")

commandSql.CommandType = CommandType.StoredProcedure;

//commandSql.Parameters.Add("@program_id", SqlDbType.VarChar, 5, programID);
commandSql.Parameters.Add("@program_id", System.Data.SqlDbType.VarChar, 5).Value = programID;

SqlParameter returnValue = new SqlParameter("@result", SqlDbType.Int);
returnValue.Direction = ParameterDirection.Output;
commandSql.Parameters.Add(returnValue);

DBAccessHelper.Execute(commandSql);

var result = returnValue.Value != System.DBNull.Value ? returnValue.Value.ToString() : string.Empty;
return result;

And here is my stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetbyProgramID]
       @program_id  varchar,
       @result int OUTPUT 
AS 
BEGIN 
    SELECT @result = system_id 
    FROM dbo.agency_mapping 
    WHERE program_id = @program_id  

    RETURN
END

I always get an empty values as the result in my C# code.

Not sure what I am doing wrong.

The values I pass are correct.

Missing anything important ?

Answer 1

You have declared

ALTER PROCEDURE [dbo].[GetbyProgramID]
       @program_id  varchar,
       @result int OUTPUT 

this means that your stored procedure receives a parameter named @program_id with size of 1 char. Probably your SELECT fails for this reason.

Try instead to declare

ALTER PROCEDURE [dbo].[GetbyProgramID]
       @program_id  varchar(5),
       @result int OUTPUT 

as from your C# code.
Do not remove the size in the C# code because it is useful to let the Sql Server Engine prepare an optimization plan that can be reused if you call your query a second time.

Mode info here: How Data Access code Affects Database Performances