Kbalz wrote:
On Oct 3, 3:38 pm, wackyph...@yahoo.com wrote:
>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.
<...>
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