Parameter Question

[Michael]

Hello,

I've noticed there are many ways to construct sql parameters, from something
simple like

cmd.Parameters.Add("@myvar", myvalue.text)

to actually specifying the sqldbtype/size such as

cmdUpdate.Parameters.Add("@name", SqlDbType.VarChar, 50, "name")

and you can get more complicated than that of course.

My question though, is there a reason I should go beyond my first example?
My queries work just fine like that, and when handling queries with many
parameters, it gets to be difficult and time consuming to have to manage all
the extra properties (though I do know ways to do it automatically). But
really, what is the benefit to it?

Thanks,
--Michael

 

[William Ryan [eMVP]]

You don't even need to specify name of the parameter if you don't want to
which would abbreviate it even further. However, it lacks clarity. If you
needed an Output Parameter or Return value, you'd need to specify it so the
first constructor wouldn't do it for you. Moreoever, the more precision you
give add, the less the server has to do to figure out what your intent is
(if you don't specify type, SQL Server has to convert it automagically) and
the less likely you'll get something in there that you don't want. I'd
liken it to using Option Strict in VB.NET...it's more verbose but makes you
intent much clearer, reduces the chances of stuff slipping through the
cracks, and doesn't force anything else to have to do extra translation to
figure out what to do.

HTH,

Bill