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 ?
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
이 기사는 인터넷에서 수집됩니다. 재 인쇄 할 때 출처를 알려주십시오.
침해가 발생한 경우 연락 주시기 바랍니다[email protected] 삭제
몇 마디 만하겠습니다