-1

I have a stored procedure call in C#. It uses ADO.NET to call the stored procedure that is on my SQL Server. Despite knowing that one of the parameters of the stored procedure on the server is VARCHAR(120), I used AddWithValue to add it to my parameter collection in C# and that has concluded that the string parameter used in C# should be translated to a SqlDbType of NVARCHAR. How do I tell if the server has used the NVARCHAR type or the VARCHAR type? I'm getting mixed messages regarding if ADO.NET is smart enough to read the correct parameter type from the server and I would like to test this empirically.

My current crude solution was as follows:

  1. Take a character that does not exist in VARCHAR, e.g. ǹ i.e. 'Latin Small Letter N With Grave'.
  2. Observe that SQL Server translates SELECT ǹ to ?.
  3. Write a query that returns values when ? is fed to it.
  4. Feed said query ǹ.
  5. Observe that the query has acted as if I fed it ?.
  6. Conclude that SQL has used VARCHAR even though I gave it NVARCHAR.
J. Mini
  • 1,868
  • 1
  • 9
  • 38
  • Have you tried creating the parameter with the `SqlParameter(String, SqlDbType, Int32)` constructor (https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter.-ctor?view=dotnet-plat-ext-8.0#system-data-sqlclient-sqlparameter-ctor(system-string-system-data-sqldbtype-system-int32) ) or something similar. Set both the type and the size of the parameter at construction time, then set its value – Flydog57 May 13 '23 at 18:19
  • @Flydog57 I know that would be the proper way to doing it, but what I want to test is what happens when I do it the improper way (with `AddWithValue`). – J. Mini May 13 '23 at 18:21
  • 2
    Sorry, can't help you. I come from the school of "if it works the way it's documented to work, that's good enough for me" :-). And, I haven't used raw ADO.NET in a decade or so. I stick with EF or Dapper – Flydog57 May 13 '23 at 18:44
  • _How do I tell if the server has used the NVARCHAR type or the VARCHAR type?_ There's really only one way to know - look at the table definition (ie: column data types) in the database which you may or may not have access to If you don't have access to the database table definition you can contact your database administrator. Alternatively, you can refer to the documentation that should have been created when the database tables were created. Whoever created the stored procedure should have verified the table definition (ie: column data types) when he/she/they created the stored procedure). – Tu deschizi eu inchid May 13 '23 at 19:14
  • @Tudeschizieuinchid So are you saying that the server **will always use the type specified in the stored procedure's parameter list**? – J. Mini May 13 '23 at 19:15
  • 1
    What I'm saying is that you may end up with a data type mismatch that can be avoided by conducting proper research before beginning. I'm also saying to _trust, but verify_. _Trust_ that someone performed his/her/their work correctly, but _verify_ it because their work will affect what you're working on. Check that the data types in the stored procedure match the data type in the database tables. Once you've done that, you should have the information you need for your application. – Tu deschizi eu inchid May 13 '23 at 19:24
  • For how to specify a Unicode string which is used with [nvarchar](https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver16) see [here](https://learn.microsoft.com/en-us/sql/t-sql/functions/nchar-transact-sql?view=sql-server-ver16#return-types) which shows that the string is prefixed with `N`. – Tu deschizi eu inchid May 13 '23 at 19:33
  • @Tudeschizieuinchid I don't care about the database tables at all. All that I'm wanting to know is how to know what parameter type the stored procedure used when the type fed to it by ADO.NET doesn't match the type in the parameter list of the stored procedure. – J. Mini May 13 '23 at 19:35
  • 2
    _I don't care about the database tables at all_: If that's the case, then you may not be the right person for this project. You need to care about the database tables - consider them the final authority. – Tu deschizi eu inchid May 13 '23 at 19:37
  • SQL Server automatically converts from the ADO.Net type to the parameter type declare in the procedure, with a slight overhead. But if you are using an ad-hoc SQL batch then that conversion will not happen (and this could affect the performance of the query) – Charlieface May 13 '23 at 21:49
  • @Charlieface If you have a source to back that up or a test that I can run that makes what you've said obviously true, then you've answered my question completely. – J. Mini May 13 '23 at 21:50
  • 1
    I don't think it needs a source, it's quite obvious that the parameter type in a stored procedure is what it is defined as, irrespective of what you send. If your procedure is declared `CREATE PROC p @param1 varchar(100) AS...` then the parameter type cannot change, and the server must coerce the data you send into that variable. You will get some conversion overhead, but it won't affect the query plan, as the query is compiled against a fixed parameter type. – Charlieface May 13 '23 at 21:54
  • @Charlieface With all the paranoia about `AddWithValue`, I'm not comfortable taking anything for granted. – J. Mini May 13 '23 at 21:57

1 Answers1

1

that has concluded that the string parameter used in C# should be translated to a SqlDbType of NVARCHAR. How do I tell if the server has used the NVARCHAR type or the VARCHAR type? I'm getting mixed messages regarding if ADO.NET is smart enough to read the correct parameter type from the server and I would like to test this empirically.

There is no such functionality. ADO.Net does not query anything from the server, it just sends whatever you tell it. If that has defaulted to nvarchar then that is what will be sent. This is why you should always specify the data types and lengths/precision/scale.

Having said that, in the case of stored procedures, the server will then convert the data back to the original type to fit into the parameter declaration, with a slight overhead, and possible loss of precision or characters etc.

Charlieface
  • 52,284
  • 6
  • 19
  • 43