none
CommandType StoredProcedure ignore sp_executesql RRS feed

  • Question

  • Good day,

    why this code generate (in sql server profiler) a 'rpc:completed exec dbo.BaTestSp @Parameter1=1234' and not 'rpc:completed exec sp_executesql ...' ?

            var connection = new SqlConnection(connectionString))
            var command = new SqlCommand("dbo.BaTestSp", connection);

            command.CommandType = CommandType.StoredProcedure;

            var parameter = new SqlParameter();
            parameter.ParameterName = "Parameter1";
            parameter.SqlDbType = SqlDbType.SmallInt;
            parameter.Direction = ParameterDirection.Input;
            parameter.Value = 1234;

            command.Parameters.Add(parameter);
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

    Thank you.


    Thursday, October 15, 2015 9:20 AM

All replies

  • Hello Paolo,

    >>why this code generate (in sql server profiler) a 'rpc:completed exec dbo.BaTestSp @Parameter1=1234' and not 'rpc:completed exec sp_executesql ...' ?

    Will the Parameter1 has the prefix “@” in your store produce? If has, please also add it to your code as:

    parameter.ParameterName = "@Parameter1";

    From MSDN:

    When using parameters with a SqlCommand to execute a SQL Server stored procedure, the names of the parameters added to the Parameters collection must match the names of the parameter markers in the stored procedure.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 16, 2015 2:00 AM
    Moderator
  • Hello, 

    thank you for the suggestion but, at my end, is not working.

    This is the sp:

     CREATE PROCEDURE TestSp

      @Par1 SMALLINT

    WITH ENCRYPTION 
    AS

    SELECT   
      NAME='ABC'

    and this is the code:

          using (var connection = new SqlConnection(connectionString))
          {
            var command = new SqlCommand();

            command.CommandText = "TestSp";
            command.CommandType = CommandType.StoredProcedure;
            command.Connection  = connection;

            var parameter = new SqlParameter();

            parameter.ParameterName = "@Par1";
            parameter.SqlDbType     = SqlDbType.SmallInt;
            parameter.Direction     = ParameterDirection.Input;
            parameter.Value         = 1234;

            command.Parameters.Add(parameter);

            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            if (reader.HasRows)
            {
              while (reader.Read())
              {
              }
            }
            else
            {
            }

            reader.Close();
          }

    and this is the trace:

    RPC:Completed - exec TestSp @Par1=1234

    Friday, October 16, 2015 5:21 AM
  • Yes, I did a test with your code and also use the sql server profiler track the generated statement, the result is the same.

    I suggest you could post this strange scenario to:

    https://connect.microsoft.com/VisualStudio

    Since according to the document, it should generate a parameterized query.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 16, 2015 9:55 AM
    Moderator