hello,
I have a problem to get return parameter from stored procedure
I want select record if it is not equal to gender which I have pass to stored procedure(I pass gender as input parameter Male or Female ).
firstly I want to check record is exist or not
If Exist Then
Check Its Gender is not equal to Gender which I pass to stored procedure.(I want to select opposite gender)
I condition is true then select record
If condition is false then return 1
If Record not exist then return 2
My code as bellow.
ALTER PROCEDURE [db_owner].[SelectProfileByID]
@ProfileID VARCHAR(6),
@UserGender VARCHAR(10),
@SelectStatus INTEGER OUTPUT
AS
BEGIN
IF EXISTS(SELECT * FROM tblUserInfo WHERE ProfileID=@ProfileID AND IsDeleted=0)
BEGIN
IF EXISTS(SELECT * FROM tblUserInfo WHERE ProfileID=@ProfileID AND UserGender != @UserGender)
BEGIN
SELECT tblUserInfo.UserAutoID, tblUserInfo.UserFirstName, tblUserInfo.ProfileID,
YEAR(GETDATE())-YEAR(UserDOB) AS UserDOB,tblUserInfo.Height,tblUserInfo.Religion, tblUserInfo.Education,tblUserInfo.Community,tblUserInfo.MaritalStatus,tblUserInfo.FamilyLocation,
tblUserInfo.Occupation,tblUserInfo.ProfilePicPath,tblInterest.InterestStatus
FROM
tblUserInfo
LEFT JOIN tblInterest
ON
tblInterest.InterestIn=tblUserInfo.UserAutoID
WHERE
tblUserInfo.ProfileID= @ProfileID
END
ELSE
BEGIN
RETURN SET @SelectStatus= 1
END
END
ELSE
BEGIN
RETURN SET @SelectStatus= 2
END
END
C# Code..
public UserInfo SearchIdInfo(string ProfileId, string Gender,ref int SelectStatus)
{
UserInfo info = new UserInfo();
SqlDataReader rdr = null;
try
{
if (sqlcon.State == ConnectionState.Closed)
{
sqlcon.Open();
}
SqlCommand cmd = new SqlCommand("db_owner.SelectProfileByID", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sprmparam = new SqlParameter();
sprmparam = cmd.Parameters.Add("@ProfileID", SqlDbType.VarChar);
sprmparam.Value = ProfileId;
sprmparam = cmd.Parameters.Add("@UserGender", SqlDbType.VarChar);
sprmparam.Value = Gender;
rdr = cmd.ExecuteReader();
if (SelectStatus == 0)
{
if (rdr.Read())
{
info.UserAutoID = rdr[0].ToString();
info.UserFirstNamea = rdr[1].ToString();
info.ProfileID = rdr[2].ToString();
info.Age = rdr[3].ToString();
info.Height = rdr[4].ToString();
info.Religion = rdr[5].ToString();
info.Education = rdr[6].ToString();
info.Community = rdr[7].ToString();
info.MaritalStatus = rdr[8].ToString();
info.FamilyLocation = rdr[9].ToString();
info.Occupation = rdr[10].ToString();
info.ProfilePicPath = rdr[11].ToString();
info.InterestStatus = rdr[12].ToString();
}
}
}
catch
{
}
finally
{
rdr.Close();
sqlcon.Close();
}
return info;
What I have tried:
You can see above code to understand what I have to trying..