How to return multiple parametes in c# using sql sp

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	--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

--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

Latest Posts