How to return multiple parametes in c# using sql sp

Posted by Karthik2010_Mca under C# on 4/16/2014 | Points: 10 | Views : 1191 | Status : [Member] | Replies : 1
Hi,

I wanted to return multiple output parameters. I have attached my code, here I can able to return only single value.

EX:
public int InsertAdmitStudent(int EnquiryId)
{
int sdetail = 0;
using (SqlConnection con = new SqlConnection(strConnection))
{
con.Open();
SqlCommand comm = new SqlCommand("uspPGInsertStudentDetailsFromOnlineEnquiry", con);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Add(new SqlParameter("@iEnquiryRegnID", SqlDbType.Int)).Value = EnquiryId;
comm.Parameters.Add("@iStudentDetailId", SqlDbType.Int).Direction = ParameterDirection.Output;
//comm.Parameters.Add("@LoginID", SqlDbType.VarChar).Direction = ParameterDirection.Output;
comm.ExecuteNonQuery();
sdetail = Convert.ToInt32(comm.Parameters["@iStudentDetailId"].Value);
//sloginid = comm.Parameters["@@LoginID"].Value.ToString();
con.Close();
}
return sdetail;
}

Karthik


Responses

Posted by: Karthik2010_Mca on: 4/16/2014 [Member] Starter | Points: 25

Up
0
Down
--Continue in above,

Hi,

The mentioned sp, It's return 2 value,

ALTER PROCEDURE [dbo].[uspPGInsertStudentDetailsFromOnlineEnquiry]
(
@iEnquiryRegnID int,
@iStudentDetailId INT OUTPUT
--@LoginID varchar(500) OUTPUT
)

AS
--SET NOCOUNT ON
BEGIN
--BEGIN TRY
DECLARE @sLoginID varchar(500)
DECLARE @iCenterID INT

--BEGIN TRANSACTION
SELECT TOP 1 @iCenterID = I_Destination_Center_ID FROM
dbo.T_Student_Registration_Details WHERE
I_Enquiry_Regn_ID = @iEnquiryRegnID
IF(@iCenterID IS NULL)
BEGIN
SELECT @iCenterID = I_Centre_Id FROM
dbo.T_Enquiry_Regn_Detail WHERE
I_Enquiry_Regn_ID = @iEnquiryRegnID
END

SELECT @sLoginID = [dbo].fnGetStudentNo(@iCenterID)

INSERT INTO T_Student_Detail
(
I_Enquiry_Regn_Id,
S_Student_Id,
I_Status,
S_Title,
S_First_Name,
S_Middle_Name,
S_Last_Name,
Dt_Birth_Date,
S_Age,
C_Skip_Test,
I_Occupation_ID,
I_Pref_Career_ID,
I_Qualification_Name_ID,
I_Stream_ID,
S_Guardian_Name,
I_Guardian_Occupation_ID,
S_Guardian_Email_ID,
S_Mobile_No,
S_Guardian_Phone_No,
I_Curr_City_ID,
I_Curr_State_ID,
S_Email_ID,
I_Curr_Country_ID,
S_Phone_No,
S_Curr_Address1,
S_Curr_Address2,
S_Curr_Pincode,
S_Perm_Address1,
S_Guardian_Mobile_No,
S_Perm_Address2,
I_Income_Group_ID,
S_Perm_Pincode,
S_Curr_Area,
I_Perm_City_ID,
I_Perm_State_ID,
I_Perm_Country_ID,
S_Perm_Area,
S_Is_Corporate,
I_Corporate_ID,
S_Conduct_Code,
S_Crtd_By,
Dt_Crtd_On
)
SELECT
@iEnquiryRegnID,
@sLoginID,
1,
S_Title,
S_First_Name,
S_Middle_Name,
S_Last_Name,
Dt_Birth_Date,
S_Age,
C_Skip_Test,
I_Occupation_ID,
I_Pref_Career_ID,
I_Qualification_Name_ID,
I_Stream_ID,
S_Guardian_Name,
I_Guardian_Occupation_ID,
S_Guardian_Email_ID,
S_Mobile_No,
S_Guardian_Phone_No,
I_Curr_City_ID,
I_Curr_State_ID,
S_Email_ID,
I_Curr_Country_ID,
S_Phone_No,
S_Curr_Address1,
S_Curr_Address2,
S_Curr_Pincode,
S_Perm_Address1,
S_Guardian_Mobile_No,
S_Perm_Address2,
I_Income_Group_ID,
S_Perm_Pincode,
S_Curr_Area,
I_Perm_City_ID,
I_Perm_State_ID,
I_Perm_Country_ID,
S_Perm_Area,
S_Is_Corporate,
I_Corporate_ID,
NULL,
'OnlineUser',
getdate()
FROM T_Enquiry_Regn_Detail
WHERE I_Enquiry_Regn_ID = @iEnquiryRegnID
SET @iStudentDetailId=@@IDENTITY

UPDATE T_Enquiry_Regn_Detail
SET I_Enquiry_Status_Code = 3,I_Centre_Id = @iCenterID
WHERE I_Enquiry_Regn_ID = @iEnquiryRegnID

INSERT INTO T_Student_Center_Detail
(
I_Student_Detail_ID,
I_Centre_ID,
I_Status,
Dt_Valid_From
)
SELECT
@iStudentDetailId,
@iCenterID,
1,
Getdate()
SELECT @iStudentDetailId AS StudentID, @sLoginID AS StudentCode
END

Exec:
[uspPGInsertStudentDetailsFromOnlineEnquiry] 577641, null

Output:
StudentID StudentCode
12345 STU124


Karthik

Karthik2010_Mca, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response