Dynamic Where Clause - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic Where Clause

Author

Topic

peddi_praveen
Starting Member

48 Posts

Posted - 2003-05-28 : 06:15:46

Hi guys,
i have a following situation:

table stru:
create tbl1
( col1 int,
col2 int,
col3 int,
col4 int,
etc1 varchar,
etc2 varchar

)

requirement should b something like this
my SP will pass me the param ,which column to b searched , accordingly , i need to use the column in my where cluase

CREATE PROC proc1
( @in_col_no int

)
AS

SELECT etc1 , etc2
FROM tbl1
WHERE
CASE @ in_col_no
WHEN 1 THEN col1 IS not nULl
WHEN 2 THEN col2 IS not nULl
WHEN 3 THEN col3 IS not nULl
WHEN 4 THEN col4 IS not nULl
END

while im trying to do this , compilation error says at "IS NOT NULL"

can we do it without dynamic sQl , (without using sp_execsql)
if so, help me.

tx in advance
praveen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-28 : 07:32:01

Since each of the possible expressions your case statement can return is a BOOLEAN expression, you are trying to return a boolean value in your CASE statement, which SQL does not allow.

YOu need to code your case something like this:

SELECT etc1 , etc2
FROM tbl1
WHERE
(@in_col_no <> 1 or (col1 is not null)) AND
(@in_col_no <> 2 or (col2 is not null)) AND
(@in_col_no <> 3 or (col3 is not null))
..etc...

Logically, this says:

If @in_col_no = 1, THEN col1 is not null AND
If @in_col_no = 2, THEN col2 is not null AND
...etc...

- Jeff

peddi_praveen
Starting Member

48 Posts

Posted - 2003-05-28 : 21:31:00

tx jeff, its working perfectly

tx n regs,
praveen kumar

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources