August 15, 2002 at 2:27 pm
Hello. I'm trying to use CASE in my code but I couldn't managed to use it right. Can you tell me what did I wrong in this SQL code? I'm having a syntax error:
selecttop 200 K.K_ID, K.K_Topic, (select U_Nickname from dbo.[MEMBERS] where U_ID=K.K_Poster) as K_LastPoster, K.K_Date,
K.K_CevapSayisi, (select U_Nickname from dbo.[MEMBERS] where U_ID=K.K_LastPoster) as K_LastPoster,
K.K_LastReplyDate,K.K_LastReplyTime
fromdbo.[TOPICS] as K left outer join dbo.[MESSAGES] as M
onK.K_ID = M.M_KonuID
wherecase @fieldToSearch
when 'ALL' then ((M.M_Message like @word) or (K.K_Topic like @word) or (K.K_Message like @word))
when 'topics'then (K.K_Topic like @word)
when 'messages'then ((M.M_Message like @word) or (K.K_Message like @word))
end
case @dateinterval
when not 'ALL' then and (K.K_Date>@date or M.M_Time>@time)
end
and K.K_Forum in @forumToSearch
and (K.K_Statu='1')
order by K.K_LastReplyDate,K.K_LastReplyTime,K.K_Date
August 15, 2002 at 4:56 pm
I think the easiest way to write this, and maintain the code going forward will be to use an IF statement and write the query three times.
If @fieldToSearch = 'ALL'
QUERY_FORMAT_1
Return
If @fieldToSearch = 'topics'
QUERY_FORMAT_2
Return
if @fieldToSearch = 'messeges'
QUERY_FORMAT_3
return
You could build a single SQL string and execute it, but that's going to get complicated and you loose the benefit of a compiled execution plan.
e.g.
set @sql = @sql + case when @fieldToSearch = 'topics' then '(k.k_topic like ''' + @word + ''')'
Good luck,
John
August 15, 2002 at 5:02 pm
One thing I would add which you need to be aware of is that putting multiple queries in a stored procedure like this is OK, but unless you use the WITH RECOMPILE option, you will only have 1 execution plan for the procedure. That may be OK if you know your queries and the search conditions are broadly similar, but if you have one query which is highly selective and one that isn't, the plan generated will not be appropriate in both cases. Just a thought,
Simon
August 20, 2002 at 5:01 pm
Or if you want to avoid the whole WITH RECOMPILE problem and the dynamic issue where query plans are not stored, then create seperate Procs for each situation then a single Proc somethings like
CREATE PROC ip_PriProc
variables here.
AS
SET NOCOUNT ON
if @fieldToSearch = 'All'
BEGIN
EXEC ip_ProcAll
END
else...
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 21, 2002 at 8:14 am
To make this work using the CASE function you can only have a column name or variable after the key word THEN. You can't have a complete comparison.
One example would be a part of your code for your case function is:
where
case @fieldToSearch
when 'ALL' then ((M.M_Message like @word) or (K.K_Topic like @word) or (K.K_Message like @word))
This should be coded as follows:
WHERE
CASE @fieldToSearch
WHEN 'ALL' THEN M.M_Message
END LIKE @word
OR CASE @fieldToSearch
WHEN 'ALL' THEN K.K_Topic
END LIKE @word
OR CASE @fieldToSearch
WHEN 'ALL' THEN K.K_Message
END LIKE @word
Robert Marda
SQL Server will deliver its data any way you want it
when you give your SQL Programmer enough developing time.
Robert W. Marda
Billing and OSS Specialist - SQL Programmer
MCL Systems
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply