With Oracle, your stored procedure's lookup dependency could be against a package variable.  You could initialize that package variable with some default value and modify it as needed.  Your would then have both the shorter signature and flexibility you desire.

<pre>

create or replace package MyPackage is

  MyPackageVariable number;

  procedure MyProcedure

  (

     param1 number

    ,param2 number

  );

end MyPackage;

create or replace package body MyPackage is

  procedure MyProcedure

  (

     param1 number

    ,param2 number

  ) is

     MyProcedureVariable MyTable.MyColumn%TYPE;

  begin

     null;

           select MyColumn

             into MyProcedureVariable

             from MyTable

            where MyTable.LookupColumn = MyPackageVariable;

  end;

begin

  -- Package Initialization

  MyPackageVariable := 0;

end MyPackage;

</pre>

Pass in XML (@xml text) and parse it in your procedure using Open XML

Couple of options I could think of. Xml text and Open Xml is certainly one of them.

Another option that I have used quite often is sending the coma seperated list of values , building a temporary table by parsing the text. There is a price to be paid by way of reduced performance ofcourse,  

Have separate stored procedure for different purpose

for e.g. GetAllOrdersWhichAreDelivered (status = 5)

GetAllOrdersWhichArePending (status = -1)

In this case, the caller need not know - what each of the status value means.

You can pass a comma separated value which contains a single ID or multiple IDs.

Select Column1,

          Column2,

          Column2,

          etc...

FROM Table1

WHERE SomeLookupType in (SELECT BigIntValue FROM CsvToBigInt(@SomePassedInValueToTheProc)

CREATE FUNCTION [dbo].[CsvToBigInt] ( @Array varchar(3999) )

returns @BigIntTable table (BigIntValue bigint)

AS

begin

 declare @separator char(1)

 set @separator = ','

 declare @separator_position int

 declare @array_value varchar(4000)

 set @array = @array + ','  

 while patindex('%,%' , @array) <> 0

 begin

   select @separator_position =  patindex('%,%' , @array)

   select @array_value = left(@array, @separator_position - 1)

   Insert @BigIntTable Values (Cast(@array_value as bigint))

   select @array = stuff(@array, 1, @separator_position, '')

 end

 return

end

>>>Have separate stored procedure for different purpose

Sure, you're gonna have this naturally.  But this can be  bad instead of just using params.  Why?  Do you want 10,000 procs in your database?  This is a management nightmare.  This is way inefficient to think that you should just create  a new proc for every type of transaction out there.

While people debate dynamic SQL, it is no longer as much as a security risk these days with SQL 2005, and if you developt your n-layers correctly, you can protect against the worries of dynamic sql pretty well.

Therefore, use Code Generated Templates and stored procs that are dynamic in nature that are bubbled up then to your templates.   This is good because:

1) Reduce the # of stored procs in your enviornment as well as repetative procs that essentially could be doing the same thing.  A lot of developers are lazy and most of us who are not lazy, don't also want to go through 10,000 procs to find out if there is an "existing" proc in the system that does what we're looking for.  If you have a controllable set of procs for different tasks, any variations that you need can be handled then through the DAL, not at the proc level...so you can reduce the # of required procs (different procs) at the DAL level by trying to reuse what procs were created by the template as much as possible rather than just hurry to just create another proc in the database.

2) You're able to control exceptions in the OO layer rather than fixing your nightmarish proc issues in the DB layer for  many reasons

3) The procs can be generated by the code generation tool.  This has tons of benefits such as consistent proc code, management in # of procs needed for templates, and overall more efficient database then and OOP layers.