Tricky Stored Procedure Question

4 Aug 2006 3:40 AM

ricardo.dapaz

Hi there, I would like to create a simple search form to allow users to

search for a job number based on a number of parameters. I think I
understand how to use parameteres associated with Stored Procedures
with a data reader to add various parameters.

However, if I have a stored procedure such as

CREATE usp_SelectfromJobNumbers (@par1 datatype, @par2 datatype, @par3
datatype)

AS

SELECT FROM Jobs WHERE
    PAR1 = @par1 AND
    PAR2 = @par2 AND
    PAR3 = @par3

I cannot just pass the one parameter to the procedure.   The question
is then: how is it possible to create a stored procedure which can
accept any number of parameters an then smartly modify the query in the

store procedure with CASE statements or similar?

I don't really want to do this as in-line code as it easily becomes
unmaintainable and it is hard to add additional conditions. I was
thinking of passing a string array into the Stored Procedure and having

the stored procedure parse through the array, determine the parameters
and do the grunt of the work. The problem is that I haven't got a lot
of experience with these advanced stored procedures using CASE
statements and the like...

Any ideas would be greatly appreciated.

Regards,

Ric

4 Aug 2006 4:07 AM

Tom Cooper

Check out http://www.sommarskog.se/dyn-search.html for methods to do this.

Tom

<ricardo.da***@gmail.com> wrote in message
Show quote

news:1154662851.972130.325620@m79g2000cwm.googlegroups.com...
> Hi there, I would like to create a simple search form to allow users to
>
> search for a job number based on a number of parameters. I think I
> understand how to use parameteres associated with Stored Procedures
> with a data reader to add various parameters.
>
> However, if I have a stored procedure such as
>
>
> CREATE usp_SelectfromJobNumbers (@par1 datatype, @par2 datatype, @par3
> datatype)
>
>
> AS
>
>
> SELECT FROM Jobs WHERE
>    PAR1 = @par1 AND
>    PAR2 = @par2 AND
>    PAR3 = @par3
>
>
> I cannot just pass the one parameter to the procedure.   The question
> is then: how is it possible to create a stored procedure which can
> accept any number of parameters an then smartly modify the query in the
>
> store procedure with CASE statements or similar?
>
>
> I don't really want to do this as in-line code as it easily becomes
> unmaintainable and it is hard to add additional conditions. I was
> thinking of passing a string array into the Stored Procedure and having
>
> the stored procedure parse through the array, determine the parameters
> and do the grunt of the work. The problem is that I haven't got a lot
> of experience with these advanced stored procedures using CASE
> statements and the like...
>
>
> Any ideas would be greatly appreciated.
>
>
> Regards,
>
>
> Ric
>

4 Aug 2006 7:19 AM

ric_deez

Hi Tom,

That is an excellent resource!!! Thanks so much for the tip...

Ric
Tom Cooper wrote:
Show quote

> Check out http://www.sommarskog.se/dyn-search.html for methods to do this.
>
> Tom
>
> <ricardo.da***@gmail.com> wrote in message
> news:1154662851.972130.325620@m79g2000cwm.googlegroups.com...
> > Hi there, I would like to create a simple search form to allow users to
> >
> > search for a job number based on a number of parameters. I think I
> > understand how to use parameteres associated with Stored Procedures
> > with a data reader to add various parameters.
> >
> > However, if I have a stored procedure such as
> >
> >
> > CREATE usp_SelectfromJobNumbers (@par1 datatype, @par2 datatype, @par3
> > datatype)
> >
> >
> > AS
> >
> >
> > SELECT FROM Jobs WHERE
> >    PAR1 = @par1 AND
> >    PAR2 = @par2 AND
> >    PAR3 = @par3
> >
> >
> > I cannot just pass the one parameter to the procedure.   The question
> > is then: how is it possible to create a stored procedure which can
> > accept any number of parameters an then smartly modify the query in the
> >
> > store procedure with CASE statements or similar?
> >
> >
> > I don't really want to do this as in-line code as it easily becomes
> > unmaintainable and it is hard to add additional conditions. I was
> > thinking of passing a string array into the Stored Procedure and having
> >
> > the stored procedure parse through the array, determine the parameters
> > and do the grunt of the work. The problem is that I haven't got a lot
> > of experience with these advanced stored procedures using CASE
> > statements and the like...
> >
> >
> > Any ideas would be greatly appreciated.
> >
> >
> > Regards,
> >
> >
> > Ric
> >

4 Aug 2006 7:08 AM

Arnie Rowland

Here is one idea that could work for you.

CREATE usp_SelectfromJobNumbers
   (   @par1 datatype = NULL
     , @par2 datatype = NULL
     , @par3 datatype = NULL
   )
AS

   SELECT
      ( ColumnList )
   FROM Jobs
   WHERE (   Par1 = coalesce( @par1, Par1 )
         AND Par2 = coalesce( @par2, Par2 )
         AND Par3 = coalesce( @par3, Par3 )
         )

Calls to the Stored Procedure should use name=value pairs for the parameters (explicit parameter names, not position).

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

<ricardo.da***@gmail.com> wrote in message news:1154662851.972130.325620@m79g2000cwm.googlegroups.com...
> Hi there, I would like to create a simple search form to allow users to
>
> search for a job number based on a number of parameters. I think I
> understand how to use parameteres associated with Stored Procedures
> with a data reader to add various parameters.
>
> However, if I have a stored procedure such as
>
>
> CREATE usp_SelectfromJobNumbers (@par1 datatype, @par2 datatype, @par3
> datatype)
>
>
> AS
>
>
> SELECT FROM Jobs WHERE
>    PAR1 = @par1 AND
>    PAR2 = @par2 AND
>    PAR3 = @par3
>
>
> I cannot just pass the one parameter to the procedure.   The question
> is then: how is it possible to create a stored procedure which can
> accept any number of parameters an then smartly modify the query in the
>
> store procedure with CASE statements or similar?
>
>
> I don't really want to do this as in-line code as it easily becomes
> unmaintainable and it is hard to add additional conditions. I was
> thinking of passing a string array into the Stored Procedure and having
>
> the stored procedure parse through the array, determine the parameters
> and do the grunt of the work. The problem is that I haven't got a lot
> of experience with these advanced stored procedures using CASE
> statements and the like...
>
>
> Any ideas would be greatly appreciated.
>
>
> Regards,
>
>
> Ric
>