Dynamic Collation in Stored Procedure

Hi,

I currently have a stored procedure used to search a large database (a few million records). A user can select a column to order by, and the sp will return results accordingly.

I wanted to avoid executing the query as a string, but as such it is a bit messy. Here is the query from the sp:

IF(@orderDirection = 'asc')
       BEGIN
            --Insert the records I want into my temp table
            INSERT INTO @tempTable
            (tmp_leadID)
            SELECT leadID FROM mgLeads
            WHERE lead_memberID = @memberID
            AND IsNull(LeadImporting,0) = 0
            AND (@showAll <> 0 OR (
                  (
                        (@field = 'firstname' AND (leadFirstName = @keywords OR (leadFirstName like '%' + @keywords + '%' AND @searchType = 'contains')))
                        OR (@field = 'lastname' AND (leadLastName = @keywords OR (leadLastName like '%' + @keywords + '%' AND @searchType = 'contains')))
                        OR (@field = 'email' AND (leadEmail = @keywords OR (leadEmail like '%' + @keywords + '%' AND @searchType = 'contains')))
                        OR (@field = 'state' AND (leadState = @keywords OR (leadState like '%' + @keywords + '%' AND @searchType = 'contains')))
                        OR (@field = 'zip' AND (leadZip = @keywords OR (leadZip like '%' + @keywords + '%' AND @searchType = 'contains')))
                  )
                  AND (leadCountry = @leadCountry OR @leadCountry = '')
                  AND (leadType = @leadType OR @leadType = '')
                  AND (leadGMSType = @leadGMSType OR @leadGMSType = '')
                  )
            )
            AND (leadStatus = @leadStatus OR @leadStatus = '')
            -- and lead has not been archived
            AND leadArchived = 0
            ORDER BY
            CASE(@orderBy)
             WHEN 'type' THEN cast(leadType as varchar)
             WHEN 'firstname' THEN cast(leadFirstName as varchar)
             WHEN 'lastname' THEN cast(leadLastName as varchar)
             WHEN 'email' THEN cast(leadEmail as varchar)
             WHEN 'phone' THEN cast(leadPhone as varchar)
             WHEN 'dateEntered' THEN dbo.FormatDate(leadStartDate,'YYYYMMDD')
             WHEN 'dateContacted' THEN dbo.FormatDate(leadLastContacted,'YYYYMMDD')
             WHEN 'rating' THEN replace(cast(leadRating as varchar),'10','99')
            END
            ASC
       END
      ELSE
       BEGIN
            --Insert the records I want into my temp table
            INSERT INTO @tempTable
            (tmp_leadID)
            SELECT leadID FROM mgLeads
            WHERE lead_memberID = @memberID
            AND IsNull(LeadImporting,0) = 0
            AND (@showAll <> 0 OR (
                  (
                        (@field = 'firstname' AND (leadFirstName = @keywords OR (leadFirstName like '%' + @keywords + '%' AND @searchType = 'contains')))
                        OR (@field = 'lastname' AND (leadLastName = @keywords OR (leadLastName like '%' + @keywords + '%' AND @searchType = 'contains')))
                        OR (@field = 'email' AND (leadEmail = @keywords OR (leadEmail like '%' + @keywords + '%' AND @searchType = 'contains')))
                        OR (@field = 'state' AND (leadState = @keywords OR (leadState like '%' + @keywords + '%' AND @searchType = 'contains')))
                        OR (@field = 'zip' AND (leadZip = @keywords OR (leadZip like '%' + @keywords + '%' AND @searchType = 'contains')))
                  )
                  AND (leadCountry = @leadCountry OR @leadCountry = '')
                  AND (leadType = @leadType OR @leadType = '')
                  AND (leadGMSType = @leadGMSType OR @leadGMSType = '')
                  )
            )
            AND (leadStatus = @leadStatus OR @leadStatus = '')
            -- and lead has not been archived
            AND leadArchived = 0
            ORDER BY
            CASE(@orderBy)
             WHEN 'type' THEN cast(leadType as varchar)
             WHEN 'firstname' THEN cast(leadFirstName as varchar)
             WHEN 'lastname' THEN cast(leadLastName as varchar)
             WHEN 'email' THEN cast(leadEmail as varchar)
             WHEN 'phone' THEN cast(leadPhone as varchar)
             WHEN 'dateEntered' THEN dbo.FormatDate(leadStartDate,'YYYYMMDD')
             WHEN 'dateContacted' THEN dbo.FormatDate(leadLastContacted,'YYYYMMDD')
             WHEN 'rating' THEN replace(cast(leadRating as varchar),'10','99')
            END
            DESC
       END

As you can see, I had to use a case statement to decide the ordering. Also, I couldn't find a way to change it from ASC to DESC dynamically and I have had to write two queries.

On top of this, I now need to change the collation based on a parameter also.

My question is, is there a way to improve on this current query (ideally compiling into one query without the case statement) and how can I change the collation based on a parameter without creating the query as a string and executing it?

Thanks.
LVL 1
LeeSwansonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
>>My question is, is there a way to improve on this current query<<
There are some very minor changes I would make, but for the most part I think you are on the right track.  For collations you will just have to increase the number of IF statements.  Not pleasant for maintenance reasons, but perfectly doable for SQL Server.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LeeSwansonAuthor Commented:
I really don't want to have to do that as I want to be able to add new languages without having to modify my SP each time.

If only there was a function that would evaluate an expression ala javascript:

ORDER BY eval(@orderField)
0
Anthony PerkinsCommented:
>>If only there was a function that would evaluate an expression ala javascript:<<
Sure there is and you already know it: Dynamic SQL.  But then (in the same way as in javascript) SQL Server would not be able to compile it and you would get the lousy performance and worse security that you were trying to avoid in the first place.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

etmendzCommented:
You don't really need to sort when you INSERT. Most of the time, the sorting may fail you because SQL Server decides when to insert what and it doesn't mean the first in the list is always the first in (for some reason). You'd rather apply sorting only when you query the temporary table.

In any case, you're query is Ok. Performance vs maintainability is really hard to tackle. If you want both, dynamic is Ok (there are still arguments about how much performance benefit stored procedures can promise over dynamic queries; it's still a trial and error case-to-case basis thing).

If you want peace, keep what you have.

Have fun.
0
LeeSwansonAuthor Commented:
The reason I am sorting before inserting into the tmp table is the results are paged. The query is working fine as it is.

I guess I will run a few tests to see how much of a performance hit it would be to have an uncompiled query. Performance is very important in this situation so I guess I may end up just using another case statement.
0
Anthony PerkinsCommented:
>>Performance is very important in this situation <<
In the unrelated category, consider using Full-Text search, instead of "LIKE".  This last may force a sequential search and on a million rows that could be a long time...
0
Anthony PerkinsCommented:
I believe I answered the question.
0
LeeSwansonAuthor Commented:
I will give it to you acperkins, but I didn't really get the information I needed. I still dont have a way to pass the collation as a parameter, or set asc/desc without using messy IF statements. I was waiting in case there was a solution.

I appreciate that there may not be a way to do this, which is why I will give the points to you.

0
Anthony PerkinsCommented:
>>but I didn't really get the information I needed.<<
Than you should have given us some feed back, that is how it works here.  Questions are considered technically abandoned after 21 days.

>>I appreciate that there may not be a way to do this, which is why I will give the points to you.<<
Kindly re-read the EE Guidelines regarding grading standards:
What's the right grade to give?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi73

In particular the following section:
<quote>
C: Because Experts' reliability are often judged by their grading records, many Experts would like the opportunity to clarify if you have questions about their solutions. If you have given the Expert(s) ample time to respond to your clarification posts and you have responded to each of their posts providing requested information; or if the answers, after clarification, lack finality or do not completely address the issue presented, then a "C" grade is an option. You also have the option here of just asking Community Support to delete the question.
Remember, the Expert helping you today is probably going to be helping you next time you post a question. Give them a fair chance to earn an 'Excellent!' grade and they'll provide you with some amazing support. It's also true that a "C" is the lowest grade you can give, and the Experts know that -- so use it judiciously.
</quote>

For the record your:
Last 10 Grades Given C A B B A A B A B C
0
LeeSwansonAuthor Commented:
I guess a B grade would have been more appropriate.

Don't get me wrong, I appreciate your time, I just never really got an answer for my question, only: do what you are already doing.

If there is a way for me to change the grade let me know.

Did you have a point about my last 10 grades?
0
Anthony PerkinsCommented:
See here:
Can I get a grade changed?
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi18

>>Did you have a point about my last 10 grades?<<
Yes.  This is what experts see before they bother to help you out.  It is in your interest to be fair.
0
Anthony PerkinsCommented:
>>I still dont have a way to pass the collation as a parameter, or set asc/desc without using messy IF statements.<<
>>I just never really got an answer for my question<<
Simply put, unless you use dynamic SQL (I do not recommend this and some shops don't even allow it) there is no other way to do this.

This is covered in the help as well.  See here:
The correct answer to some questions is "you can't do that"
http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi54
0
LeeSwansonAuthor Commented:
Fair enough. I have requested that the grade be changed.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.