one sql parameter to give me 3 potential values

Question

In my stored procedure I need a single parameter that will give me a potential of 3 values.

So I can do something like:

@p1  <-- my parameter


IF (@p1 ???)  -- include users
     SELECT * FROM USERS


IF (@p1 ???) -- include employees
    SELECT * FROM employees

IF (@p1 ???) -- iclude customers
    SELECT * FROM CUSTOMERS

I am guessing I will have to do some bit banging, but not sure how to do it in sql.

Update

I am actually doing a UNION of sort.

A stored procedure that returns different shaped result sets depending on parameters will wreck havoc with most client tools. You sure you want it to behave like that? — Remus Rusanu, Nov 07 '09 at 03:28

score 2 · Answer 1 · edited Jul 21 '10 at 19:10

Rather than have the T-SQL inline, you are often better off creating separate stored procedures and calling them.

This is because there is only one cached query plan per batch.

Also, take heed of Remus's comment: you are breaking the Single Responsibility Rule. This makes maintenance error prone.

It might be a different story if you are always returning the same multiple results sets (MARS)...

score 1 · Accepted Answer · answered Nov 07 '09 at 03:22

1

If @p1 is an int, or some variant of it like bigint or tinyint, you could probably use bitwise and.
Here is an example:

IF ( @p1 & 1 = 1)
IF ( @p1 & 4 = 4)

answered Nov 07 '09 at 03:22

Corey Sunwold

10,194
6
51
55

one sql parameter to give me 3 potential values

2 Answers2