SP Parameters - default values and parameter order

[Just D]

I know that when we write a Stored Procedure we can add a default value to
the parameter so that if it's not set when we call this SP the default value
should be used. It gives me some additional flexibility and a simple way to
manipulate the same SP in different ways.

I also know that in most cases it's not critical to change the order of the
SP parameters. For example if we're having the SP with these parameters
(don't take it close, it's just an example ):

@PatientName varchar(255),
@Complaint varchar(255),
@ProviderName varchar(255)

we can add these parameters to the SqlParameter[] sp = ... object in a
different order comparing to the order implemented in the SP. It's
convenient for our implementation. But what I notices is that I can't change
the order of the parameters whenever I want to. Sometimes the order is
critical and I guess depends on the parameter type, but I can't understand
what's the real reason and limitations for that.

Is anybody having this kind of experience?

Thanks,
Just D.

 

[William \(Bill\) Vaughn]

The order of Parameters is determined by the provider. Each has its own
requirements. With SQL Server (and ADO.NET) one can provide the parameters
in any order as long as the name matches the name specified in the SP
definition on the server. In ADO classic one could only use named parameters
if you asked for that feature explicitly. In other providers like ODBC and
OLE DB, the name is irrelevant but the position is fixed.

hth

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)

 

[Just D]

"William (Bill) Vaughn"

The order of Parameters is determined by the provider. Each has its own
requirements. With SQL Server (and ADO.NET) one can provide the parameters
in any order as long as the name matches the name specified in the SP
definition on the server. In ADO classic one could only use named
parameters

No, that's why I asked. I saw several exclusions from this rule when I was
not able to change the order of the parameters. And my original question
was - who knows when exactly this rule doesn't work.

if you asked for that feature explicitly. In other providers like ODBC and
OLE DB, the name is irrelevant but the position is fixed.

Anyway thanks for answering.

Just D.

 

[RobinS]

"William (Bill) Vaughn"

No, that's why I asked. I saw several exclusions from this rule when I
was not able to change the order of the parameters. And my original
question was - who knows when exactly this rule doesn't work.

What provider were you using when you saw the exclusions? Because Bill is
right (of course) -- the order does not matter when using SQLServer.

 

[Just D]

"RobinS"

What provider were you using when you saw the exclusions? Because Bill is
right (of course) -- the order does not matter when using SQLServer.

I'm working with C# code, using ADO.NET with SqlHelper class against MS SQL
Server 2000 with all SPs installed. And I noticed that sometimes we're
having exclusions from the main rule - the order affects the query and it
generates an exception. Maybe it's because of the default parameters that
I'm using in the SP. I will show an example when I'm able to reproduce this
issue again.

Thanks,
Just D.