Monday, July 30, 2012

Declaring OUT parameter in SQL and to retrive it

public int CheckalreadyExists(string Name)
        {
            param = new SqlParameter[2];
            param[0] = new SqlParameter("@Name", Name);
            param[1] = new SqlParameter("@IsFound", SqlDbType.Int);
            param[1].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteDataset(oDBConnection.ConString(), CommandType.StoredProcedure, "SPR_CheckalreadyExists", param);
            return Convert.ToInt32(param[2].Value.ToString());
        }

CREATE PROCEDURE SPR_CheckalreadyExists(
    @ID INT,   
    @IsFound TINYINT OUT
)
AS
BEGIN
    IF EXISTS(SELECT ID FROM StudentTable WHERE Name = @Name )
        BEGIN
            SET @IsFound = 1
        END
    ELSE
        BEGIN
            SET @IsFound = 0
        END
END

No comments:

Post a Comment