How to get values of output parameters in c# while executing SP using ExecuteScalar()

Question

I am trying to execute a stored procedure in c#.

Stored procedure performs insert operation and returns values of one of the columns as output parameter.

I need to execute it from c# and get back value of output param.

Below is what i have tried so far.

SqlParameter[] associateParams = new SqlParameter[10];
            {
                 associateParams[0]=new SqlParameter("@orgName", newAssociate.OrgName);
                 associateParams[1]=new SqlParameter("@createdBy", newAssociate.Email);
                 associateParams[2]=new SqlParameter("@userName", newAssociate.UserName);
                 associateParams[3]=new SqlParameter("@workEmail", newAssociate.Email);
                 associateParams[4]=new SqlParameter("@password", newAssociate.Password);
                 associateParams[5]=new SqlParameter("@teamStrength", "0");
                 associateParams[6]=new SqlParameter("@projName", newAssociate.ProjName);
                 associateParams[7]=new SqlParameter("@userType", "Associate");
                 associateParams[8] = new SqlParameter("@userSalt", SqlDbType.VarChar, 400);
                 associateParams[8].Direction = ParameterDirection.Output;
                 associateParams[9] = new SqlParameter("@activationKey", SqlDbType.Int);
                 associateParams[9].Direction = ParameterDirection.Output;

            }

        using (SqlCommand cmd = con.CreateCommand())
        {
            log.Debug("In command is called");
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = ProcedureName;
            cmd.Parameters.AddRange(param);

            log.Debug("Command is called");
            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();

                    log.Debug("Con is open");
                }
                cmd.ExecuteScalar();
                log.Debug(cmd.Parameters["@userSalt"].Value.ToString());
                log.Debug(cmd.Parameters["@activationKey"].Value.ToString());

Executing above, performs insert successfully but returns null for output params values.

Can anyone suggest what I am missing here.

Thanks

You need to declare parameter which will receive return value, and set its direction as returnvalue. Check for solution here: http://stackoverflow.com/questions/706361/getting-return-value-from-stored-procedure-in-c-sharp — OctoCode, Oct 25 '16 at 13:02
Try like this. associateParams[9] = new SqlParameter("@activationKey", SqlDbType.Int); associateParams[9].Value = -1; associateParams[9].Direction = ParameterDirection.Output; — James, Oct 25 '16 at 13:23
Do you set the value of the parameter in the stored procedure? Do the parameters have a value when calling the SP from SSMS? — Dirk, Oct 25 '16 at 13:30
@Dirk. yes, I am setting the values... but even while running from studio, it is not returning anything.. — Dheeraj Kumar, Oct 25 '16 at 13:31
Remember the law of output parameters: 1- direction, 2 - for string parameters - size. — T.S., Oct 25 '16 at 18:22

Badiparmagi · Accepted Answer · 2016-10-25T13:56:22.050

0

try like this when you define output parameters:

         associateParams[8] = new SqlParameter("@userSalt", SqlDbType.VarChar, 400);
         associateParams[8].Value = "";
         associateParams[8].Direction = ParameterDirection.Output;
         associateParams[9] = new SqlParameter("@activationKey", SqlDbType.Int);                 
         associateParams[9].Value = 0;
         associateParams[9].Direction = ParameterDirection.Output;

let me know if this helps.

UPDATE: here is my own method

 cmd.Parameters.Add(new SqlParameter("@userSalt", SqlDbType.VarChar, 400));
 cmd.Parameters["@userSalt"].Value = "";
 cmd.Parameters["@userSalt"].Direction = ParameterDirection.Output;

UPDATE1: because you dont use ExecuteNonQuery. Change cmd.ExecuteScalar() to cmd.ExecuteNonQuery()

edited Oct 25 '16 at 13:56

answered Oct 25 '16 at 13:21

Badiparmagi

1,285
1
14
24

@DKR i have also use something like this in one of my projects to read my output but i didn't use `SqlParameter[]`. I used SqlCommand. Check my updated answer – Badiparmagi Oct 25 '16 at 13:32
@DKR, it seems updated from my PC. updated part: UPDATE: here is my own method cmd.Parameters.Add(new SqlParameter("@userSalt", SqlDbType.VarChar, 400)); cmd.Parameters["@userSalt"].Value = ""; cmd.Parameters["@userSalt"].Direction = ParameterDirection.Output; – Badiparmagi Oct 25 '16 at 13:36
1

@DKR because you dont use ExecuteNonQuery. Change `cmd.ExecuteScalar();` to `cmd.ExecuteNonQuery();` – Badiparmagi Oct 25 '16 at 13:44
thats right.. coz executescalar doesnt return output values... changing it to executenonquer worked. thanks for reminding.! – Dheeraj Kumar Oct 25 '16 at 13:54
@DKR glad you help. updated my answer. can you mark it as answer please? – Badiparmagi Oct 25 '16 at 13:56
@DKR, what else did you change because `ExecuteScalar` will still work, anything that executes the command will set the OUTPUT parameters. – Crowcoder Oct 25 '16 at 14:00
I have to downvote because future googlers should not think `ExecuteScalar` cannot work with output params. – Crowcoder Oct 25 '16 at 14:18
@Crowcoder you can not get output value with `ExecuteScalar`. have a look at [here](http://stackoverflow.com/questions/2974154/what-is-the-difference-between-executescalar-executereader-and-executenonquery) – Badiparmagi Oct 25 '16 at 14:24
@Badiparmagi, yes you can.The result of the procedure has nothing to do with output values. [See this explanation of parameter behavior](https://contrivedexample.com/2016/10/22/input-output-and-inputoutput-sqlparameter-behavior-explained/). – Crowcoder Oct 25 '16 at 14:26
I didn't make any other changes... However some forums says that it doesn't work with executescalar when you are trying to return output params. – Dheeraj Kumar Oct 25 '16 at 18:25
@Crowcoder yes, thats the reason you are wrong. he does not want result of procedure. he wants ouput result that he sets in procedure. anyway. have a nice day, sir. – Badiparmagi Oct 26 '16 at 05:31
@badimparmagi that is exactly what I'm saying. He wants output not result. Check my blog post, you can run the unit tests as proof. You can do ExecuteScalar, ExecuteNonQuery, ExecuteReader, even fill an adapter. It doesn't matter, output params will be set if stored procedure sets them. – Crowcoder Oct 26 '16 at 09:07
@Badiparmagi, I carefully read through the link you posted. It does not mention anything about the use of OUTPUT parameters, or any of the `ParameterDirection`'s, yet you are saying I misunderstand the question. – Crowcoder Oct 26 '16 at 12:23
from Tim Schmelter comment: "With ExecuteScalar you can retrieve a return value not an output parameter.". Read comment under this answer: [answer](http://stackoverflow.com/a/10905807/2332844) – Badiparmagi Oct 26 '16 at 12:49
I'm saying the execution method has nothing to do with output values. Tim is correct, but it is also correct to say ExecuteNonQuery doesn't either. None of the execute methods retrieve output parameter values, the ado.net provider sets the value on the parameter instance. It would take you 5 minutes to try it. I conjecture OP changed both the code and the procedure at the same time and attributed success to the code change. – Crowcoder Oct 26 '16 at 14:55

How to get values of output parameters in c# while executing SP using ExecuteScalar()

1 Answers1