Determining a SQL Server datatype length

[Guest]

I have a database that holds large varchar data. The table has a column of
type varchar(8000). The stored procedure that is called to add the data to
the table also has a paramater of type varchar(8000). The stored procedure
is wrapped within a .net web service. I would like the web service to check
the length of the data passed to it and throw an exception if the data is
greater than 8000 characters long. But I don't want to hard code the 8000
byte limit into the web service. .net has MaxValue properties for simple
datatypes like int. System.Data.SqlDbType.VarChar.MaxValue does not exist,
however. Is there a way to get the maximum length of the VarChar datatype
from .net or better yet, the maximum accepted length of a varchar parameter
of a particular stored procedure?

 

[Mary Chipman [MSFT]]

String.Length will give you the number of characters in a string. Is
that what you were looking for>

--Mary

On Thu, 3 Feb 2005 07:19:06 -0800, "Joe Monnin" <Joe

 

[NuTcAsE]

Joe, to determine the max length of a parameter on a stored proc during
runtime you will first need to get the schema of the stored proc and
then check the length of the parameter.

You can do this by quering the INFORMATION_SCHEMA.PARAMETERS view. This
view provides information on the various parameters defined on sprocs.
For example:

SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME =
'MyProcName'

This will return you a result set contianinng the parameters for the
proc, its data type and length. So you could expand that sql statement
to get the length of the parameter. For ex:

SELECT CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = 'MyProcName' AND PARAMETER_NAME = '@name'

That select statement will return the maximum character length of a
varchar (or char) parameter. Then you can check for the length without
hard coding 8000.

PS. Dont forget to put the @ in the where condition.

NuTcAsE

 

[Guest]

No, what I'm after is the number of characters allowed in the stored
procedure paramater. For example, say I have a stored procedure:

create procedure sp_Proc1
@someparam varchar(30)
as
update tblWhatever set col1=@someparam

From my .net app, I want to find out how many characters I can pass to the
@someparam parameter in sp_Proc1 (30 in this case). If I pass a 60 character
string to this stored procedure, the last 30 characters will be dropped and
no exception will be raised, although one probably should. But since it's
not, I want to check to make sure that the string I want to pass to the
stored procedure is not longer than the stored procedure will accept.

 

[Guest]

Thanks. That will work. However, if I now move my system to antoher
database, the whole thing will break. It's not as simple as replacing the
SqlClient library with the AnotherDatabaseClient library. Is this the only
way to do it, or is there some any more general .net way of doing it that's
not dependent upon a particular database (or at least supported within the
..net classes for that database)?

 

[NuTcAsE]

Hey Joe,

Well the approach I suggested is very SQL Server centric. In any case
which ever database you do use, you will first need to get the schema
of the stored proc and check out its parameter lengths. Im not sure how
that is achieved on other databases, but you can abstract the
functionality of getting the parameter lengths into a seperate project.

It would work something like this:

1. Main application calls GetParameterLength ("paramName", "sprocName",
DbConnection connection) :int of lets say a project called DbSchema.dll
2. DbSchema project first checks the provider type of the connection
(currently in 1.1 you can do this by checking its type).
3. If the connection is of SqlConnection, then you execute the above
example and get the parameter lenth. If its say OracleConnection, you
use an oracle specific query (im sure you can get schema information
for oracle too). Basically a switch statement to execute the correct
query.

Apart from that AFIK there is not .net generic way to get the schema
information of a stored proc. Sorry

NuTcAsE

 

[Guest]

Apparantly, Microsoft has already thought about this. I just discoverd that
ADO.net 2.0 will support a GetSchema() method found in the connection class
that will provide a .net generic way to get the size of table columns. The
article (http://www.devx.com/dbzone/Article/27131/0/page/1) does not mention
if stored procedure metadata will also be available....Very interesting.