How to create statements without `N` character in parameter

Question

I have following ADO.Net C# code for executing stored procedure with parameters. When I observe the profiler it is listed as

 exec uspSecurityGetChildActivitiesByInternalName @ActivityInternalName = N'INVWLVIEWEDIT'

But I want it to be modified as

  exec uspSecurityGetChildActivitiesByInternalName @ActivityInternalName = 'INVWLVIEWEDIT'

I need to avoid the N before parameter value. This should be possible because in the original trace I have, it is coming without N (though I don’t have the source code for that).

How can we modify the following C# code to create exec statement in profiler without N?

REFERENCE

Code:

using (SqlCommand command = new SqlCommand(spName, connection))
{
    WriteLogFunction(spStatement);
    command.CommandType = System.Data.CommandType.StoredProcedure;

    if (paramsList.Count > 0)
    {
       foreach (Parameter p in paramsList)
       {
           command.Parameters.AddWithValue(p.MyParameterName, p.MyVal);
       }
    }

    string resultVal=String.Empty;

    using (SqlDataReader reader = command.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                resultVal = reader.GetString(0);
            }
        }
    }

    //Other code  
}

@YuriyGalanter I need this to work with the default setting; without specifying unicode — LCJ, Apr 29 '14 at 18:11
Are all the paramters passed to SP strings? What is their size as defined in the SP? — Yuriy Galanter, Apr 29 '14 at 18:14
You say you want and you say you need but you don't say why. It might be easier to make helpful suggestions if we understand you better. — TaW, Apr 29 '14 at 18:16
@TaW I am trying to execute all SPs from a trace file. Refer http://stackoverflow.com/questions/2086713/can-sql-profiler-display-return-result-sets-alongside-the-query — LCJ, Apr 29 '14 at 21:19

score 4 · Answer 1 · answered Apr 29 '14 at 18:23

You can force non-Unicode parameter, by specifying it's type as VarChar:

command.Parameters.AddWithValue(p.MyParameterName, p.MyVal).SqlDbType = SqlDbType.VarChar;

This should pass parameter without 'N' - but if not all your parameters are string - you will have to add checks so integers and the rest won't get screwed.

score 1 · Answer 2 · answered Apr 29 '14 at 18:24

Assuming your stored procedure for some reason requires an ANSI string and not a Unicode String:

In your code you are letting the type of the parameter be inferred from the value passed. .NET Strings are Unicode strings so the parameter type is inferred as a Unicode String (which leads to the N''). For the parameters that you need to be non-Unicode you need to set the parameter's DbType property to AnsiString or the SqlDbType to Text (as opposed to NText). (Or yes...as Yuri said, VarChar is probably what you want.)

The SQLParameter object is returned from your call to AddWithValue, so you can set it then if need be.

I didn't test this with your code, so hopefully I'm not missing something, but that is how you specify the type of the parameter.

How to create statements without `N` character in parameter

2 Answers2