?
Solved

Dynamic Collation in Stored Procedure

Posted on 2004-11-22
15
Medium Priority
?
732 Views
Last Modified: 2008-02-07
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.
0
Comment
Question by:LeeSwanson
  • 7
  • 5
13 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 750 total points
ID: 12650930
>>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
 
LVL 1

Author Comment

by:LeeSwanson
ID: 12651176
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12651224
>>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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 6

Expert Comment

by:etmendz
ID: 12651929
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
 
LVL 1

Author Comment

by:LeeSwanson
ID: 12651939
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12655149
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12889562
I believe I answered the question.
0
 
LVL 1

Author Comment

by:LeeSwanson
ID: 12889777
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12890023
>>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
 
LVL 1

Author Comment

by:LeeSwanson
ID: 12890219
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12890299
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 12890312
>>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
 
LVL 1

Author Comment

by:LeeSwanson
ID: 12890333
Fair enough. I have requested that the grade be changed.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question