How to exec stored Procedure which has more than one outparameter in C#

Question

CREATE PROCEDURE Proc_SelectDetails
@id int,
@Name varchar(50) out,
@RollNo varchar(50) out  
AS 
BEGIN
      set @name = (select  from table where id= @id)
      set @RollNo  = (select  from table where id= @id)

End

I can not do any changes in sp.I have to use this sp as it in my C# code. If I try to call sp with of help of Dataset.Getting dataset has empty.Even Datareader is also come as empty. Which is proper method to call so that I will get Output parameter value in C#.

Possible duplicate of [Calling stored procedure with return value](https://stackoverflow.com/questions/6210027/calling-stored-procedure-with-return-value) — Cid, Sep 26 '19 at 08:59

score 1 · Answer 1 · answered Sep 26 '19 at 09:30

just create a connection, command and add your parameters as appropriate - e.g.:

         using (var connection = new SqlConnection(@"connection string..."))
         {
            connection.Open();
            var command = new SqlCommand("Proc_SelectDetails", connection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add("id", SqlDbType.Int);
            command.Parameters["id"].Value = 234;

            var name = command.Parameters.Add("Name", SqlDbType.VarChar, 50);
            name.Direction = ParameterDirection.Output;

            var rollNo = command.Parameters.Add("RollNo", SqlDbType.VarChar, 50);
            rollNo.Direction = ParameterDirection.Output;

            command.ExecuteNonQuery();

            Console.WriteLine(name.Value);
            Console.WriteLine(rollNo.Value);

            }

score 1 · Answer 2 · answered Sep 26 '19 at 09:52

if you are using 3-Tier architecture, then in the DAL(data access layer) you can get the OUT values very easily by following code.

public string selecteddetails (DOMAR SD)
    {
     DbCommand dbCommand = db.GetStoredProcCommand("Proc_SelectDetails");
     string out_name;
     string out_rollno;
     db.AddInParameter(dbCommand, "@id", DbType.Int32, SD.CaseID);
     db.AddOutParameter(dbCommand, "@NAME", DbType.String, 10);
     db.AddOutParameter(dbCommand, "@ROLLNO", DbType.String, 10);
     db.ExecuteNonQuery(dbCommand);
     out_name= (string)db.GetParameterValue(dbCommand, "@NAME");
 out_rollno= (string)db.GetParameterValue(dbCommand, "@ROLLNO");
     return out_name+out_rollno;
    }

there are so many others method thru which you can get the values. the only common thing is you have to declare the @name,@rollno in C# code.

If u like my code please hit like.. if u want another code tell me, i will send u another one.

How to exec stored Procedure which has more than one outparameter in C#

2 Answers2