Prevent ADO.NET/SQL Server from truncating parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a setting in either ADO.NET or SQL Server 2000 that will allow me to receive an error when a parameter is passed to a stored procedure and the parameter is truncated?

For instance:

CREATE PROC foo ( parm varchar(30)) AS ....

If I make the call to the stored proc and the value passed is greater than 30, the value is truncated and the procedure
executes but there is no warning or indication that the stored procedure call truncated the input.

Very annoying! and very hard to diagnose a bug
 
Hi,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know how to be warned when
the stored procedure is going to truncate a parameter which is longer than
the limitation. If there is any misunderstanding, please feel free to let
me know.

As far as I know, this cannot be done both in ADO.NET and SQL Server 2000.
If a parameter's value is longer than its size, the rest part is truncated
automatically. The truncation process is transparent to the user. So, if
you would like to check the size of the input, it is better to do this in
the presentation layer of the application. That means to check the string
length as soon as the user has input it. Hope this helps for you.

Does this answer your question? If anything is unclear, please feel free to
reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Kevin

Thank you for your reply -- you captured the understanding of my question correctly

Is there an Email address at Microsoft where I can request a feature change for SQL Server

The issue is that there could be possibly multiple sources that are using a DAAB or Database Stored Procedures especially as companies and enterprises start to consolidate systems and as more people start using DotNet which makes code reuse easier. One cannot always assume the consumer of a layer will use it properly. While we could add defensive coding at the lower layers, these are things that belong at the Business or User layer. It would be nice if I could have a switch that I can put into a stored procedure when I "explicitly" wanted a range check performed on the input. This is possible in Oracle, DB2 and other databases. It is great to assume that things happen correctly, but this is not the case

I don't know if Yukon will do things different but I would like to make my case to a product manager if that is possibl

Thank yo
Enz

----- Kevin Yu [MSFT] wrote: ----

Hi

Thank you for posting in the community

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know how to be warned when
the stored procedure is going to truncate a parameter which is longer than
the limitation. If there is any misunderstanding, please feel free to let
me know

As far as I know, this cannot be done both in ADO.NET and SQL Server 2000.
If a parameter's value is longer than its size, the rest part is truncated
automatically. The truncation process is transparent to the user. So, if
you would like to check the size of the input, it is better to do this in
the presentation layer of the application. That means to check the string
length as soon as the user has input it. Hope this helps for you

Does this answer your question? If anything is unclear, please feel free to
reply to the post

Kevin Y
======
"This posting is provided "AS IS" with no warranties, and confers no
rights.
 
Hi Enzo,

I'm not quite sure if this feature will be changed in next version of SQL
Server. Before the release of the product, we cannot promise anything.

If you have suggestions that could make our products better, please send
mail directly to (e-mail address removed). Your suggestions will be highly
appreciated. Thank you very much for your feedback!

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top