case statement with an sql query statement

a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do??Originally posted by ONIL
a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do??

can you show the statement you are trying to execute? Or a better understanding of what you are after?|||ok here's the issue
i have the following: -
1. a access table: telephone_directory (fields are first name, last name, extension_no, building_name)
2. a form with menu find and sub menus "by extension number", "by first name", "by last name". ALSO another form named PF with a text box and a listbox

3. a sql parameter query as: -
cq.SQL = "PARAMETERS something1 INTEGER; SELECT * FROM TELEPHONE_DIRECTORY" & _
" WHERE LEFT(EXTENSION_NO,1) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,2) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,3) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,4) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,5) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,6) LIKE [something1] " & _
" OR EXTENSION_NO LIKE [something1] " & _
" ORDER BY EXTENSION_No; "
4. a bas file with the parameter query wherein case 1 is for extension number case 2 is for last names wherein the sql query is : -

cq.SQL = "PARAMETERS something1 text; SELECT * FROM TELEPHONE_DIRECTORY" & _
" WHERE LEFT(LAST_NAME,1)LIKE [something1] " & _
" OR LEFT(LAST_NAME,2)LIKE [something1] " & _
" OR LEFT(LAST_NAME,3)LIKE [something1] " & _
" OR LEFT(LAST_NAME,4)LIKE [something1] " & _
" OR LEFT(LAST_NAME,5)LIKE [something1] " & _
" OR LEFT(LAST_NAME,6)LIKE [something1] " & _
" OR LAST_NAME LIKE [something1] " & _
" ORDER BY LAST_NAME; "
.... and so on

5. now how do i call for the "case" in the PF form so that the text box takes the input and listbox displays result for all types of find. I am succesful with different forms for each FIND but i want to use ONLY ONE form.|||Since it's an Access database I don't know if IIf() or Switch() would help at all.sql

Case Expression within a Stored Proc

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
BillOn 8 Sep 2004 06:44:18 -0700, Bill Willyerd wrote:

>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

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)|||On 8 Sep 2004 08:17:57 -0700, Bill Willyerd wrote:

>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

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)|||On 8 Sep 2004 16:28:26 -0700, Bill Willyerd wrote:

>At present it comes as a single request.
(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)