Wildcard Parameters?

12 Aug 2005 11:21 AM

CJM

I have a SP where I want the user to be able to supply a parameter that
determines what criteria to apply to a Bit field:

Eg.

Create Proc MyProc
@val bit
As
Select * from MyTable
where BitField = @Val

This is fine as it is, but I want to be able to search where BitField = 1,
BitField = 2 or BitField = either...

I can currently handle this by providing two logical routes through the SP:
one for if @val is 0 or 1, and another if it is 0 (ie 'allow either').

But can I streamline this some way so that I only have one bit of code that
handles the either/or/any situation?

Thanks

Chris

--
cjmnew***@REMOVEMEyahoo.co.uk
[remove the obvious bits]

12 Aug 2005 11:30 AM

Lee-Z

something like this?
--------------------------------
Create Proc YourProc (@CheckVal Tinyint=0)

As

Select * from MyTable
where BitField = @CheckVal OR @Checkval = 0
----------------------------------

Lee-Z

PS. How can you check on 2 if your param is a bit....? Just curious....

Show quote

"CJM" <cjmnews04@newsgroup.nospam> wrote in message
news:%23OdsLAznFHA.3316@TK2MSFTNGP14.phx.gbl...
>I have a SP where I want the user to be able to supply a parameter that
>determines what criteria to apply to a Bit field:
>
> Eg.
>
> Create Proc MyProc
> @val bit
> As
> Select * from MyTable
> where BitField = @Val
>
> This is fine as it is, but I want to be able to search where BitField = 1,
> BitField = 2 or BitField = either...
>
> I can currently handle this by providing two logical routes through the
> SP: one for if @val is 0 or 1, and another if it is 0 (ie 'allow either').
>
> But can I streamline this some way so that I only have one bit of code
> that handles the either/or/any situation?
>
> Thanks
>
>
> Chris
>
>
>
>
> --
> cjmnew***@REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>

12 Aug 2005 11:42 AM

CJM

"Lee-Z" <Lee-Z.spam@zonnet.nl> wrote in message
news:usZBWEznFHA.3568@TK2MSFTNGP10.phx.gbl...
>
> PS. How can you check on 2 if your param is a bit....? Just curious....
>

The code example posted was the either/or variant - as opposed to the
either/or/any variant, which would abviously need a tinyint parameter

12 Aug 2005 11:37 AM

John Bell

Hi

Assuming that passing NULL for @val says you want either and that bitfield
is constrained to 0 and 1
then

Select * from MyTable
where BitField = @Val
OR @val IS NULL

would do what you want.

John

Show quote

"CJM" wrote:

> I have a SP where I want the user to be able to supply a parameter that
> determines what criteria to apply to a Bit field:
>
> Eg.
>
> Create Proc MyProc
> @val bit
> As
> Select * from MyTable
> where BitField = @Val
>
> This is fine as it is, but I want to be able to search where BitField = 1,
> BitField = 2 or BitField = either...
>
> I can currently handle this by providing two logical routes through the SP:
> one for if @val is 0 or 1, and another if it is 0 (ie 'allow either').
>
> But can I streamline this some way so that I only have one bit of code that
> handles the either/or/any situation?
>
> Thanks
>
>
> Chris
>
>
>
>
> --
> cjmnew***@REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>
>

12 Aug 2005 11:43 AM

CJM

Thanks Lee/John...

It was an embarrassingly obvious solution in the end! lol

Chris