October 20, 2004 at 10:18 pm
Dear All,
Can i change the where clause operator dynamically without using dynamic sql
i want to switch between "=" and "like" depending upon the value of a variable using single query
declare @switch int
set @switch = 0
if @switch = 0
select * from history where name like 'abc%'
else
select * from history where name = 'abc%'
can i do this in one statement
b'coz i need to handle more operators
thanks in advance
Arun
October 21, 2004 at 12:29 am
October 21, 2004 at 4:43 am
I think U can use case statement here to combine two queries in one.
October 22, 2004 at 12:12 am
This worked for me
DECLARE @switch int
declare @name varchar(60)
set @name = 'ZNG AI%'
Set @switch=0
SELECT * FROM history
WHERE 1 =
CASE @switch
WHEN 0 THEN CASE WHEN [name] like @name THEN 1 ELSE 0 END
WHEN 1 THEN CASE WHEN [name]=@name THEN 1 ELSE 0 END
WHEN 2 THEN CASE WHEN [name]<> @name THEN 1 ELSE 0 END
else 1
END
regards
Arun
October 22, 2004 at 6:36 am
Another option is to modify the search string and use a single query.
For example, compare these two SELECT statements:
SELECT * FROM history WHERE name LIKE 'ABC%' -- pattern matching
SELECT * FROM history WHERE name LIKE 'ABC[%]' -- % treated as a literal
------------------------------------------------------------------
Here's how I might implement it.
DROP TABLE pct
GO
CREATE TABLE pct (id int identity(1,1) primary key, s varchar(10))
SET NOCOUNT ON
INSERT pct (s) VALUES ('ABC')
INSERT pct (s) VALUES ('ABC%')
INSERT pct (s) VALUES ('DEF')
INSERT pct (s) VALUES ('GHI')
INSERT pct (s) VALUES ('GHI%')
INSERT pct (s) VALUES ('GHIJ')
SET NOCOUNT OFF
SELECT * FROM pct
DECLARE @switch int, @strToFind varchar(10)
SET @strToFind = 'ABC%'
SET @switch = 1 -- this would be the passed parameter
IF @switch = 1
SET @strToFind = REPLACE (@strToFind, '%', '[%]')
SELECT * FROM pct WHERE s LIKE @strToFind
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply