SQLParameter Question.

[wackyphill]

There are sever overloads and ways of adding to the SqlCommand's
Parameters collection. They all specify the parameters name. But some
also supply a SqlDbType and a length. Does anyone know the pros/cons
of supplying or not supplying this info?

 

[Kbalz]

There are sever overloads and ways of adding to the SqlCommand's
Parameters collection. They all specify the parameters name. But some
also supply a SqlDbType and a length. Does anyone know the pros/cons
of supplying or not supplying this info?

I've always specified both the SqlDbType and Length.

One advantage I can think of is you will avoid type errors.. for
instance say your Sql Column is DATETIME type, and you try to insert a
string "abc" into that column. If you specify the SqlDbType in
your .NET code, it will catch this error before sending the query to
the server (I think)

Another advantage for the length.. Say your Sql Column is
varchar(40).. and you don't specify the length of the varchar in
your .net. You could pass a string to the SqlParam's Value that is
longer than 40 characters, and the .NET will not care, but Sql Server
will raise a warning about the data being truncated.

Now if you DID specify the SqlParam's length to be 40, then .NET will
automatically truncate the string to 40 characters for you.. and no
error will be raised (but you may loose data and not know it).


I'm interested in other's responses.. it is pretty tedious to type
every Sql Type and Length when working with stored procs.

 

[John B]

I've always specified both the SqlDbType and Length.

I'm interested in other's responses.. it is pretty tedious to type
every Sql Type and Length when working with stored procs.

I've never bothered but it has bitten me, twice.

Once when using the mysql driver and passing an initial value of null
(then populating it with the "real" value in a loop), it was not
inferring the correct type.

The second time was just recently with MSSQL 2005 SP1 and I had a very
interesting situation where I was retrieving data based on a varchar param.
When you dont specify the type as varchar, the command interpret's it as
nvarchar (makes sense since .net is unicode).
99.9% of the cases (all x 1) have worked fine, but in this case, sql
server would not match the where clause nvarchar param to the varchar
column values. It would randomly match 1 or 2 out of maybe 100.
Analysing the sql it uses sp_executesql and passes nvarchar params which
when run manually would fail the same as my program.
I tested against sp2 and it worked but it would in some cases work on
our test systems but not on prod.
Anyhoo, when I specified the type as varchar, it worked 100%.

I still dont bother specifying type or length.



JB

 

[wackyphill]

Yeah, I've wondered if it may be faster to specify it. I don't know if
ADO.NET does reflection to find the type of object you pass it if you
don't explicitly tell it what it is or if it just can call toString()
on everything no matter what.

I wish MSDN was a bit more detailed about it, because I don't mind
doing it if it helps, but I often wonder if I'm doing more work for no
reason