April 9, 2012 at 1:11 pm
Hi,
I have a proc like this:
CREATE PROCEDURE GetSearchText
@Filter VARCHAR (50), @Context VARCHAR (50)
AS
BEGIN
IF (@Context = 'a')
BEGIN
SELECT DISTINCT col1 FROM Table1
WHERE col1 LIKE @Filter + '%';
END
ELSE
IF (@Context = 'b')
BEGIN
SELECT DISTINCT col2 FROM Table1
WHERE col2 LIKE @Filter + '%';
END
END
Is there any way to write this script without having if.. else statement?
I am trying to get col1 or col2 matching the pattern based on the context. This query works fine, but looking for alternative way.
April 9, 2012 at 2:16 pm
Why are you looking for an alternative way? Is this query slow? Do you not like IF ELSE? If it works, why change it?
Jared
CE - Microsoft
April 10, 2012 at 8:00 am
This proc will be called for auto-complete feature and while typing each character it will be called. The context will be set based on a dropdown value. Initially I thought of handling it in business layer which will have separate procs for each context, but I am prevented to add new procs as the count will be increased if new context is added. From UI, I feel retreiving records fro auto-complete is bit slow.
April 10, 2012 at 8:05 am
I think for auto-complete to work effectively, the entire data set should be loaded for the context and then filtered programmatically as each letter is typed. Not calling a stored proc each time a letter is added...
Jared
CE - Microsoft
April 10, 2012 at 8:12 am
SathishK (4/10/2012)
This proc will be called for auto-complete feature and while typing each character it will be called.
That's a very good way to really slow a database down...
Does the app absolutely call for autocomplete and can the possible values not be cached in the application?
btw... http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2012 at 10:50 am
The data is being cached in client end, but for first time login and if the cache is cleared, it will be problem.
I think for auto-complete to work effectively, the entire data set should be loaded for the context and then filtered programmatically as each letter is typed. Not calling a stored proc each time a letter is added...
That's a great idea and I am wondering why I hadn't thought of it. :w00t:
April 10, 2012 at 10:53 am
GilaMonster (4/10/2012)That's a very good way to really slow a database down...
Does the app absolutely call for autocomplete and can the possible values not be cached in the application?
That's an old app done in that way and I m new to it. I will re-write the biz logic.
April 10, 2012 at 11:09 pm
You could use CASE statements in the proc because you were only using the IF statments to select the column. This might make it a little easier to maintain but I agree with the others in thinking that there is a fundamental design problem that needs to be addressed.
SELECT DISTINCT
CASE WHEN @Context = 'a' THEN col1
WHEN @Context = 'b' THEN col2
END
FROM @Table1
WHERE
CASE WHEN @Context = 'a' THEN col1
WHEN @Context = 'b' THEN col2
END
LIKE @Filter + '%'
Chuck Hoffman
------------
I'm not sure why we're here, but I am sure that while
we're here we're supposed to help each other
------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply