WHERE clause in sp w/parameter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

geekboy0001@yahoo.com - 17 May 2005 20:25 GMT

I need to do this but can't figure out the correct syntax... any ideas?
(yes, I know this is wrong, but it's just pseudocode so you get the
idea):

CREATE PROCEDURE foo @Param1 Integer AS
SELECT myField
FROM myTable
If (@Param1 = 1) OR (@Param1 = 2) Begin
WHERE someField IN (1,2)
End
Else Begin
WHERE someField = @Param1
End
AND someOtherField = Whatever
AND someOtherThing = Whatever

Reply to this Message

Bill Karwin - 17 May 2005 22:08 GMT

> I need to do this but can't figure out the correct syntax... any ideas?
> (yes, I know this is wrong, but it's just pseudocode so you get the
[quoted text clipped - 11 lines]
> AND someOtherField = Whatever
> AND someOtherThing = Whatever

Right; you can't put clauses of a SQL query into expressions. The only
way around this is to build up the query string by parts, and then use
the equivalent of EXECUTE IMMEDIATE to execute that string as fetch the
result set. Only I infer from the "@" syntax that you're use MS SQL
Server, and SQL Server doesn't seem to have any facility for executing
dynamic queries like that in Transact-SQL.

I would solve your problem as follows:

IF (@Param1 = 1 OR @Param1 = 2)
SELECT myField
FROM myTable
WHERE someField IN (1, 2)
AND ...;
ELSE
SELECT myField
FROM myTable
WHERE someField = @Param1
AND ...;

The reason to break it up is for maintainability. In my experience,
this kind of special-case handling tends to become more complex as your
application gets used over time. Lacing the SQL query with logic
statements will become unmaintainable very soon, but using IF/ELSE
constructions allows you much greater freedom to use any condition to
run any query, and each query will be more readable.

Regards,
Bill K.

Reply to this Message

Bill Karwin - 17 May 2005 23:25 GMT

> ...to execute that string as fetch the result set.

Excuse my typo. That should read, "to execute that string and fetch the
result set."

Regards,
Bill K.

Reply to this Message

geekboy0001@yahoo.com - 18 May 2005 00:22 GMT

Well, there is a way -- I can use the eval() function... create a
massive string of the desired SQL statement and pass that to eval() but
I think that's simply slow and useless. Damn, I thought I was missing
something.
Ah well...
Thanks!

Reply to this Message

--CELKO-- - 18 May 2005 16:29 GMT

Use a case expression that returns one or zero depending on the
predicates in the WHEN clause. You still have a problem thinking in a
declararive language. Even in your pseudo-code, you call a column a
field!

CREATE PROCEDURE Foo (@param1 INTEGER)
AS
SELECT MyField
FROM MyTable
WHERE SomeOtherField = Whatever
AND SomeOtherThing = Whatever
AND CASE
WHEN @param1 IN (1, 2)
AND SomeColumn IN (1,2)
THEN 1
WHEN @param1 NOT IN (1, 2)
AND SomeColumn = @param1
THEN 1 ELSE 0 END = 1;

But at least you try to avoid dynamic SQL.

Reply to this Message

geekboy0001@yahoo.com - 19 May 2005 00:54 GMT

Yeah, yeah... column, field... picky picky... :)
I see what you're suggesting and it's very interesting. I'll give it a
shot, thanks. By the way, you're not Joe Celko, are you? As in -- SQL
For Smarties, the one-and-only book I worship and dream of when I sleep
at night? Okay, I lied, but still it's what got me started all those
years ago. Anyway, if it is really you, I commend you on one of the
best books around.

Reply to this Message

--CELKO-- - 19 May 2005 04:41 GMT

>> Yeah, yeah... column, field... picky picky... <<

No, it really is important. If you think with the right mental model,
you see the answers orders of magnitude easier. I have been teaching
SQL for over a decade; I have seen the difference.

>> By the way, you're not Joe Celko, are you? <<

It is a dirty job, but soemone hs to do it.

>> As in -- SQL For Smarties, the one-and-only book I worship and dream

of when I sleep at night? <<

I like to go to sleep with a couple of chapters from THE STORY OF O or
THE ILLUSTRATED HISTORY OF FLOGGING IN THE BRITISH NAVY volumes 7 thru
12 myself, but to each his own.

>> Okay, I lied, .. <<

Me, too. It is 120 DAYS OF SODOM by DeSade.

Reply to this Message

Paul - 19 May 2005 18:04 GMT

>Me, too. It is 120 DAYS OF SODOM by DeSade.

Not Justine?

Paul...

Signature

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.2.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, As a courtesy to those who spend
time analyzing and attempting to help, please
do not top post.

Reply to this Message

Nis Jorgensen - 21 May 2005 19:54 GMT

>>> Yeah, yeah... column, field... picky picky... <<
>
>No, it really is important. If you think with the right mental model,
>you see the answers orders of magnitude easier.

To quote David Hilbert: "One must be able at any time to replace
points, lines and planes with tables, chairs and beer mugs."

Or in other words: The WORDS used are not defining the mental model.
When people tell you (as people have done in this newsgroup
repeatedly) that they use "field" and "record" to mean exactly the
same as you mean by "column" and "row", there is no reason to keep
telling them that there is something wrong with their mental model.

You may say that there is something wrong with their vocabulary, of
course. Then we can have a nice long fight about words.

/Nis

Reply to this Message

--CELKO-- - 22 May 2005 01:28 GMT

>> When people tell you (as people have done in this newsgroup

repeatedly) that they use "field" and "record" to mean exactly the same
as you mean by "column" and "row", there is no reason to keep telling
them that there is something wrong with their mental model. <<

Then what words do they invent for field and record, which also occur
in data bases? Since they cannot distinquish between logical and
physically levels, which Dr. Codd says is vital, I think it is fair to
say that they missed something at the foundations. Their mental model
is mired in contigous, sequential storage and not in sets and
abstractions. After teaching SQL/RDBMS in industry and colleges for
15+ years and writing books on the subject, I have found that it makes
a BIG difference. What do base your opinion on?

Reply to this Message

geekboy0001@yahoo.com - 23 May 2005 17:32 GMT

If I may just interject here for a second, being the original poster
and recipient of the "insult" ... I don't feel it's worth debating. I
know what I know, and if Joe feels the need to correct my usage, he has
the right. I'm not offended. I've been using SQL for a decade and I'm
the one everyone turns to across three companies of SQL programmers for
help... I've never met a query I couldn't write, and write well. This
was just a simple question that has been stumping me for some time now.
I'm comfortable enough in my abilities not to be offended at the
remark that I have something wrong with my mental model -- I know I
don't. I think Joe is getting technical with me because he deals with
people every day who are coming from xBase thinking or with very little
formal training, and it's hard to tell the difference here in a
newsgroup. He doesn't know me, doesn't know my graduate history or
that I was a teacher's assistant in a college data structures course in
college, and he doesn't know anything about the project I'm working on.
Yes, I slipped up and called a column a field, but either way I got
the answer I was looking for -- that I'm not crazy and this is a tough
problem to solve. I do happen to agree with Nis in that it may have
been interpreted as insulting and a bit cocky, but you're sitll my
hero, Joe!

Reply to this Message