Dealing to a SQL SP with lots of params

[Aussie Rules]

Hi,

I have about 30 sql SP with around 50 paramaters each.

Currently in each SP, each param has its own name (prmC, prmD etc). Just
out of being a bit lazy, is there away that each param can have the same
name.. just savings me from having to rename each param as I go (i am cut
and past a single block over and over as many times as needed for each SP)

Thanks

Dim prmC = New SqlParameter("@City ", Data.SqlDbType.NVarChar,
50)
prmC.Value = strRole
SqlCommand.Parameters.Add(prmC)

Dim prmD = New SqlParameter("@State ", Data.SqlDbType.NVarChar,
50)
prmD.Value = strCountry
SqlCommand.Parameters.Add(prmD)

Dim prmE = New SqlParameter("@country", Data.SqlDbType.NVarChar,
50)
prmE.Value = strCountry
SqlCommand.Parameters.Add(prmE)

 

[Jack Jackson]

Hi,

I have about 30 sql SP with around 50 paramaters each.

Currently in each SP, each param has its own name (prmC, prmD etc). Just
out of being a bit lazy, is there away that each param can have the same
name.. just savings me from having to rename each param as I go (i am cut
and past a single block over and over as many times as needed for each SP)

Thanks

Dim prmC = New SqlParameter("@City ", Data.SqlDbType.NVarChar,
50)
prmC.Value = strRole
SqlCommand.Parameters.Add(prmC)

Dim prmD = New SqlParameter("@State ", Data.SqlDbType.NVarChar,
50)
prmD.Value = strCountry
SqlCommand.Parameters.Add(prmD)

Dim prmE = New SqlParameter("@country", Data.SqlDbType.NVarChar,
50)
prmE.Value = strCountry
SqlCommand.Parameters.Add(prmE)

There is no need to keep references to all of the parameters.

Dim prm as SqlParameter

prm = New SqlParameter(...)
prm.Value = ...
SqlCommand.Parameters.Add(prm)

prm = New SqlParameter(...)
prm.Value = ...
SqlCommand.Parameters.Add(prm)
....

 

[Guest]

I have about 30 sql SP with around 50 paramaters each.

Currently in each SP, each param has its own name (prmC, prmD etc).
Just out of being a bit lazy, is there away that each param can have
the same name.. just savings me from having to rename each param as I
go (i am cut and past a single block over and over as many times as
needed for each SP)

Have you looked into a DAL framework like LLBLGen Pro or similar? These
applications hide the need to write low level SQL code yet still provides
you with very powerful features.

Microsoft has their own framework coming out (Linq/Dlinq) but it won't be
available till next year.

 

[Steven Cheng[MSFT]]

Hi Aussie,

As for the following request you mentioned::
is there away that each param can have the same name..
<<<<<<<<

do you mean you want to make all those SqlParameter for the store procedure
share the same variable name? If so, I think the means Jack mentioned is
t he reasonable approach, you can only define a single SqlParameter
variable reference and reuse it for all the parameters in your store
procedure(or sql statement). However, you still need to create
instance(with the proper parameters in construtor) since each parameter
have different name and type in your database defined store procedure.
Does this help you?

If you have any more specific question or anything we missed, please feel
free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


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

 

[Steven Cheng[MSFT]]

If Aussie,

Does the suggetion about reuse a single SqlParameter object helps you here?
If there is any further questions on this, please feel free to post here.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


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

 

[Aussie Rules]

Hi,

Another thanks for helping me solve this.

THanks