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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy