Execute Stored Procedure with parameters and return value

Question

I am trying to execute a stored procedure that takes parameters and return a parameter. When I run the procedure separately in Management Studio everything works OK. But if I try to run the procedure from the code I am getting the "Execution Timeout Expired" error. I am suspecting that I am not passing the output parameter correctly:

List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@BankType", "Checking"));
parameters.Add(new SqlParameter("@VendorEmail", "vendorEmail@yahoo.com"));
parameters.Add(new SqlParameter("@ID", 12345));

SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@Confirm";
outputParameter.SqlDbType = System.Data.SqlDbType.Bit;
outputParameter.Direction = System.Data.ParameterDirection.Output;

parameters.Add(outputParameter);

myEntity.Database.ExecuteSqlCommand("exec TestStoredProc @BankType, @VendorEmail, @ID, @Confirm out", parameters.ToArray());

Possibly similar to https://stackoverflow.com/questions/22068027/executing-sql-stored-procedure-with-output-parameter-from-entity-framework — adityap, Sep 11 '18 at 18:14
I looked at your suggestion and the suggestion didn't solve my issue. — Lio Programista, Sep 11 '18 at 18:25
Why would you see an execution timeout and assume that it's because you're passing parameters wrong? If it says it's an execution timeout, you should assume that's exactly what it is unless you can prove otherwise. Does the error always happen after a set amount of seconds? Have you tried increasing the timeout? How long does it take to execute when you run it via SSMS? — mason, Sep 11 '18 at 18:44

score 2 · Accepted Answer · answered Sep 12 '18 at 08:59

Try this approach:

 var bankParameter =
                   new SqlParameter("@BankType", SqlDbType.VarChar) { Value = "Checking" };

        var emailParameter =
            new SqlParameter("@VendorEmail", SqlDbType.VarChar) { Value = "vendorEmail@yahoo.com" };

        var idParameter =
            new SqlParameter("@ID", SqlDbType.Int32) { Value = 12345 };

        var conStr = "yourConnectionString";
        using (SqlConnection sConn = new SqlConnection(conStr))
        {
            using (SqlCommand sComm = new SqlCommand("TestStoredProc", sConn))
            {
                sComm.CommandTimeout = 60;
                sComm.CommandType = CommandType.StoredProcedure;

                sComm.Parameters.Add(bankParameter);
                sComm.Parameters.Add(emailParameter);
                sComm.Parameters.Add(idParameter);

                var returnParameter = sComm.Parameters.Add("@Confirm", SqlDbType.Bit);
                returnParameter.Direction = ParameterDirection.ReturnValue;


                sConn.Open();

                //// NonQuery
                sComm.ExecuteNonQuery();
                var result = returnParameter.Value;

            }
        }

This piece of code have some flaws (such as no conStr given or wrong data types), but should work if you adjust them to your needs. Main difference is that my Direction is

returnParameter.Direction = ParameterDirection.ReturnValue;

and your is

outputParameter.Direction = System.Data.ParameterDirection.Output;

Also, I added CommandTimeout = 60, because, it is possible, that there is nothing wrong with your code, but your SP is just way too long.

score 1 · Answer 2 · answered Sep 11 '18 at 18:31

1

Can't comment due to reputation.

But your code does not add the outputParameter to parameters.

answered Sep 11 '18 at 18:31

FernandoG

460
5
16

I missed pasting this line of code. It is edited now. Still the same error. – Lio Programista Sep 11 '18 at 18:37
The code looks correct then. Have you checked your connection string to see if your program can connect to the server? – FernandoG Sep 11 '18 at 18:46
Yes I am doing different calls to the database. And the rest is working fine – Lio Programista Sep 11 '18 at 18:57

Execute Stored Procedure with parameters and return value

2 Answers2