CODEWITHSUNDEEP
sqlstored-proceduresoutput
kiro karam
kiro karam

Reputation: 99

Get the value from Output parameter C#

hi i'm try to create sp in sql with output value
this is the code 

ALTER PROCEDURE [dbo].[usp_SelectHospital_IfExiste_Department]
@HospitalDepartmentID INT,
@IfExiste INT OUTPUT 
AS

    SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF NOT EXISTS  (SELECT c.DeptID FROM Clinic c WHERE DeptID=@HospitalDepartmentID )
BEGIN


    SET @IfExiste=0

SELECT
    [HospitalDepartmentID],
    [NAME]
FROM
    [dbo].[Hospital_Department]
WHERE
    [HospitalDepartmentID] = @HospitalDepartmentID

END

ELSE
BEGIN
    SET @IfExiste=1

SELECT
    [HospitalDepartmentID],
    [NAME]
FROM
    [dbo].[Hospital_Department]
WHERE
    [HospitalDepartmentID] = @HospitalDepartmentID
    END

and the C# code 

public static Hospital_Department GetDepartmentInfo(int ID,int OutIfExist)
        {
            SqlCommand cmd;
            SqlDataReader dr;
            Hospital_Department HD = new Hospital_Department();
            using (cmd = new SqlCommand("usp_SelectHospital_IfExiste_Department", ProjectCon.GetCon()))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@HospitalDepartmentID", ID);
                //cmd.Parameters.Add("@IfExiste",SqlDbType.Int).Direction = ParameterDirection.Output;
                cmd.Parameters.Add("@IfExiste",SqlDbType.Int);
                cmd.Parameters["@IfExiste"].Direction = ParameterDirection.Output;
                dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    HD.NAME = dr["NAME"].ToString();
                    HD.HospitalDepartmentID = Convert.ToInt32(dr["HospitalDepartmentID"]);
                }
                OutIfExist = Convert.ToInt32(cmd.Parameters["@IfExiste"].Value);
                return HD;
            }

        }

when i try to get the output value is always null and i run the stored procedure in sql Was run and return the value so plz tell me what's wrong in my code thx

Upvotes: 1

Views: 4714

Answers (1)

Felipe Pereira
Felipe Pereira

Reputation: 12320

Maybe this question has useful info:

According to, http://msdn.microsoft.com/en-us/library/ms971497, you must close the datareader before you process the output parameters.

Hope it helps.

Upvotes: 1

Related Questions