Maximum number of parameters for a procedure

[Leroy]

Hello,

I have a question regarding the maximum number of parameters that can be
passed to a procedure.

In VB 6 the max was 60.

What is the max for Dot Net?

please and thanks.

Leroy

 

[Marina]

There is no maximum as far as I know.

However, if you are getting even close to passing 60 arguments to a method,
it's probably time to rethink the design of your app.

 

[Jay B. Harlow [MVP - Outlook]]

Leroy,
I don't know what the actual maximum number of parameters to a procedure is,
or if there is any "attainable" limit.

However! surely there is a logical maximum that one should observe!!

I find with more then a handful (3 or 4) of parameters that I seriously
consider using a Parameter Object!

http://www.refactoring.com/catalog/introduceParameterObject.html

A benefit of introducing a parameter object, is the chance that some of the
behavior from your first class can be moved to the parameter class.

Of course methods that have ParamArray parameters can have 5 or more
parameters passed...

Hope this helps
Jay


| Hello,
|
| I have a question regarding the maximum number of parameters that can be
| passed to a procedure.
|
| In VB 6 the max was 60.
|
| What is the max for Dot Net?
|
| please and thanks.
|
| Leroy
|
|

 

[Leroy]

Thanks Marina.

The procedure is a creation of my dba and he's a little tempermental when it
comes to suggestions such as redesign.
I'm in the process of converting apps to dot net from vb 6 and this one is
on the list, but the current production copy has been modified to more than
60 parameters and hasn't been converted. I'm trying to determine if I should
expidite the conversion, or do some sort of work around.

Leroy

 

[Marina]

Are you talking about a SQL Server stored procedure, or a VB method? I am
not clear.

If this is about a stored procedure, then you are limited by what SQL Server
limits you to - in which case VB 6 and VB.NET would not be a factor at all.

If this is a VB method, then I dont' see why your DBA would be a factor
here.

But in my opinion, in either case, no procedure should have 60 arguments.

 

[Cor Ligthert [MVP]]

Jay,

However! surely there is a logical maximum that one should observe!!

I find with more then a handful (3 or 4) of parameters that I seriously
consider using a Parameter Object!

My thought first as well, however thinking about the result after that do I
keep it with Marina's answer.

(I thought about writing it than as a kind of intermediate, however I did
not find even that the right answer. I assume that we do not much misagree
about this)

Or I shall oversee of course something.



Cor

 

[Leroy]

The procedure is a wrapper for an oracle database procedure. We have a
program that generates our procedure code to use in our apps and that is
where the problem lies. The dba creates a "stub" for the database proc and
the program reads the stub and generates the proc for vb along with
parameters and data types for those parameters. The program usually works
fine, except in this case. The dba doesn't want to split the stub, therefore
our canned app creates a wrapper procedure with 60 + parameters.

I'll use a parameter array for the vb6 problem and use a parameter object
for the conversion to dot net.

thanks for your help

L

 

[Leroy]

Thanks,

I'm going to follow the parameter array route to fix this problem and use a
parameter object when converting the app to dot net.

Leroy

 

[Herfried K. Wagner [MVP]]

I have a question regarding the maximum number of parameters that can be
passed to a procedure.

In VB 6 the max was 60.

What is the max for Dot Net?

The maximum is rather irrelevant, because no sane person would design a
method with more than "a few" parameters because of usability issues arising
from a large number of parameters.

 

[Joe T.]

With regard to the tempermental DBA tell him that his job is to
administer the database, not define your client application
architecture. He should not be in the business of mandating how you
call stored procedures.

There is no excuse for passing 60 arguments to a method, or a single
stored procedure for that matter. In fact, even storing 60 fields in
a single table is usually indicative of a non-normalized database, so
it sounds like he is trying to force you to plod forward and further
exacerbate what is already a flawed database design.

It behooves us as developers to push back on this type of nonsense.
Go over his head if you have to... get him fired if you have to.

Understanding the root cause of a problem is the first step to solving
it. Your problem is not the limit imposed by VB or DotNet. Your
problem is a DBA on crack.

 

[Jay B. Harlow [MVP - Outlook]]

Leroy,
Using the following System.CodeDom, I was able to generate routines with
32768 & 65536 parameters each. However VS.NET 2003's IDE really didn't like
the routine, although I did not receive any compile errors...

Imports System.CodeDom

Dim member As New CodeMemberMethod
member.Attributes = MemberAttributes.Public Or
MemberAttributes.Final
member.Name = "ExcessiveParameters"
For index As Integer = 1 To 32 * 1024
Dim parameter As New
CodeParameterDeclarationExpression(GetType(String), "Parm" & index)
member.Parameters.Add(parameter)
Next
type.Members.Add(member)

I suspect the actual limit is line length within the IDE and/or compiler,
rather then actual number of parameters...

Post if you would like a more complete sample of the above CodeDom code.

I would recommend the SYstem.CodeDom namespace for your program that
generates your procedure code...

Hope this helps
Jay

| The procedure is a wrapper for an oracle database procedure. We have a
| program that generates our procedure code to use in our apps and that is
| where the problem lies. The dba creates a "stub" for the database proc and
| the program reads the stub and generates the proc for vb along with
| parameters and data types for those parameters. The program usually works
| fine, except in this case. The dba doesn't want to split the stub,
therefore
| our canned app creates a wrapper procedure with 60 + parameters.
|
| I'll use a parameter array for the vb6 problem and use a parameter object
| for the conversion to dot net.
|
| thanks for your help
|
| L
|
| | > Are you talking about a SQL Server stored procedure, or a VB method? I
am
| > not clear.
| >
| > If this is about a stored procedure, then you are limited by what SQL
| > Server limits you to - in which case VB 6 and VB.NET would not be a
factor
| > at all.
| >
| > If this is a VB method, then I dont' see why your DBA would be a factor
| > here.
| >
| > But in my opinion, in either case, no procedure should have 60
arguments.
| >
| > | >> Thanks Marina.
| >>
| >> The procedure is a creation of my dba and he's a little tempermental
when
| >> it comes to suggestions such as redesign.
| >> I'm in the process of converting apps to dot net from vb 6 and this one
| >> is on the list, but the current production copy has been modified to
more
| >> than 60 parameters and hasn't been converted. I'm trying to determine
if
| >> I should expidite the conversion, or do some sort of work around.
| >>
| >> Leroy
| >>
| >> | >>> There is no maximum as far as I know.
| >>>
| >>> However, if you are getting even close to passing 60 arguments to a
| >>> method, it's probably time to rethink the design of your app.
| >>>
| >>> | >>>> Hello,
| >>>>
| >>>> I have a question regarding the maximum number of parameters that can
| >>>> be passed to a procedure.
| >>>>
| >>>> In VB 6 the max was 60.
| >>>>
| >>>> What is the max for Dot Net?
| >>>>
| >>>> please and thanks.
| >>>>
| >>>> Leroy
| >>>>
| >>>
| >>>
| >>
| >>
| >
| >
|
|

 

[Jay B. Harlow [MVP - Outlook]]

Playing with this a little more, 32768 did not give any compile errors.

65536 caused the compiler to throw an exception...

Of course if you really have 32768 parameters, you really *really* should
review your design ;-)

Jay

| Leroy,
| Using the following System.CodeDom, I was able to generate routines with
| 32768 & 65536 parameters each. However VS.NET 2003's IDE really didn't
like
| the routine, although I did not receive any compile errors...
|
| Imports System.CodeDom
|
| Dim member As New CodeMemberMethod
| member.Attributes = MemberAttributes.Public Or
| MemberAttributes.Final
| member.Name = "ExcessiveParameters"
| For index As Integer = 1 To 32 * 1024
| Dim parameter As New
| CodeParameterDeclarationExpression(GetType(String), "Parm" & index)
| member.Parameters.Add(parameter)
| Next
| type.Members.Add(member)
|
| I suspect the actual limit is line length within the IDE and/or compiler,
| rather then actual number of parameters...
|
| Post if you would like a more complete sample of the above CodeDom code.
|
| I would recommend the SYstem.CodeDom namespace for your program that
| generates your procedure code...
|
| Hope this helps
| Jay
|
| || The procedure is a wrapper for an oracle database procedure. We have a
|| program that generates our procedure code to use in our apps and that is
|| where the problem lies. The dba creates a "stub" for the database proc
and
|| the program reads the stub and generates the proc for vb along with
|| parameters and data types for those parameters. The program usually works
|| fine, except in this case. The dba doesn't want to split the stub,
| therefore
|| our canned app creates a wrapper procedure with 60 + parameters.
||
|| I'll use a parameter array for the vb6 problem and use a parameter object
|| for the conversion to dot net.
||
|| thanks for your help
||
|| L
||
|| || > Are you talking about a SQL Server stored procedure, or a VB method? I
| am
|| > not clear.
|| >
|| > If this is about a stored procedure, then you are limited by what SQL
|| > Server limits you to - in which case VB 6 and VB.NET would not be a
| factor
|| > at all.
|| >
|| > If this is a VB method, then I dont' see why your DBA would be a factor
|| > here.
|| >
|| > But in my opinion, in either case, no procedure should have 60
| arguments.
|| >
|| > || >> Thanks Marina.
|| >>
|| >> The procedure is a creation of my dba and he's a little tempermental
| when
|| >> it comes to suggestions such as redesign.
|| >> I'm in the process of converting apps to dot net from vb 6 and this
one
|| >> is on the list, but the current production copy has been modified to
| more
|| >> than 60 parameters and hasn't been converted. I'm trying to determine
| if
|| >> I should expidite the conversion, or do some sort of work around.
|| >>
|| >> Leroy
|| >>
|| >> || >>> There is no maximum as far as I know.
|| >>>
|| >>> However, if you are getting even close to passing 60 arguments to a
|| >>> method, it's probably time to rethink the design of your app.
|| >>>
|| >>> || >>>> Hello,
|| >>>>
|| >>>> I have a question regarding the maximum number of parameters that
can
|| >>>> be passed to a procedure.
|| >>>>
|| >>>> In VB 6 the max was 60.
|| >>>>
|| >>>> What is the max for Dot Net?
|| >>>>
|| >>>> please and thanks.
|| >>>>
|| >>>> Leroy
|| >>>>
|| >>>
|| >>>
|| >>
|| >>
|| >
|| >
||
||
|
|