Case Expression within a Stored Proc

[Bill Willyerd]

Is it possible? I have a request to create a stored proc that will
dynamically add a range to a WHERE clause based on a numeric value of a
comment type. If the incoming comment type request is say 10, the
where clause needs to be set to IN(10,11,12,13, 14,15,16,17,18, 19)OR if
a 20 is passed in the clause would read IN(20,21.....)
So I was thinking that a CASE expression within the proc would be the
best way to go, but have had no luck in finding an example or any other
related information regarding CASE exp in a proc.
TIA
Bill

[Hugo Kornelis]

Re: Case Expression within a Stored Proc

On 8 Sep 2004 06:44:18 -0700, Bill Willyerd wrote:
[color=blue]
>Is it possible? I have a request to create a stored proc that will
>dynamically add a range to a WHERE clause based on a numeric value of a
>comment type. If the incoming comment type request is say 10, the
>where clause needs to be set to IN(10,11,12,13, 14,15,16,17,18, 19)OR if
>a 20 is passed in the clause would read IN(20,21.....)
>So I was thinking that a CASE expression within the proc would be the
>best way to go, but have had no luck in finding an example or any other
>related information regarding CASE exp in a proc.
>TIA
>Bill[/color]

Hi Bill,

In this case (no pun intended), I'd simply write it like this:

WHERE MyColumn BETWEEN @parameter AND @parameter + 9

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

Re: Case Expression within a Stored Proc

On 8 Sep 2004 08:17:57 -0700, Bill Willyerd wrote:
[color=blue]
>Sorry I didn't add that if a request for specfic comment type comes in
>like 22 we only return the type 22's.
>I do like the BETWEEN stmt though I haven't seen that before, I will
>remember that one.
>
>Thx, Bill[/color]

Hi Bill,

Is the request for "a specific comment type" passed in through a seperate
parameter? And the other parameter is used to get a range of comment
types?

CREATE PROC MyProc @Specific int,
@RangeStart int
AS
IF (@Specific IS NULL AND @RangeStart IS NULL)
OR (@Specific IS NOT NULL AND @RangeStart IS NOT NULL)
RAISERROR ('Supply exactly one of the two parameters', 16, 1)
ELSE
IF @Specific IS NOT NULL
SELECT Column List
FROM YourTable
WHERE MyColumn = @Specific
ELSE
SELECT Column List
FROM YourTable
WHERE MyColumn BETWEE @RangeStart AND @RangeStart + 9
go
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Hugo Kornelis]

Re: Case Expression within a Stored Proc

On 8 Sep 2004 16:28:26 -0700, Bill Willyerd wrote:
[color=blue]
>At present it comes as a single request.[/color]
(snip)

Hi Bill,

How do you know if the request is for a specific comment type or for a
range? Surely, there has to be some way to distinguish a request for
comment type '20' (meaning just 20) from a request for comment type '20'
(meaning all values 20 through 29).

Maybe this is a good time to explain what information should be included
in newsgroup postings to maximise the chance to get a useful reply:

* Table structure, posted as DDL (CREATE TABLE statements, omitting
irrelevant columns but including all constraints);
* Sample data, posted as INSERT statements (and please verify that the
CREATE TABLE and INSERT statements you post work properly in an empty test
database!);
* Expected output, based on sample data;
* The SQL code you already got (if any), plus the results these give you
and the reason why that is wrong. If you get an error message, copy and
paste the full message;
* A short, concise description of the business problem you're trying to
solve.

Check out these sites as well:

http://www.aspfaq.com/etiquette.asp?id=5006

http://vyaskn.tripod.com/code.htm#inserts

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)