March 28, 2014 at 10:39 am
I have this Order By statement:
ORDER BY
CASE WHEN @SortID = 1 AND @SortDirection = 'ASC' THEN A.ProjectID END ASC,
CASE WHEN @SortID = 1 AND @SortDirection = 'DESC' THEN A.ProjectID END DESC
I have another column called A.DateSort. What I need is:
WHEN @SortID = 1 AND @SortDirection = 'ASC' ... I want to sort by A.ProjectID ASC, A.DateSort ASC
WHEN @SortID = 1 AND @SortDirection = 'DESC' ... I want to sort by A.ProjectID DESC, A.DateSort ASC
i.e. Regardless of whether ProjectID is being sorted ASC or DESC, I want the records to also be sorted by DateSort ASC
How can I get 'DateSort' into the ORDER BY / CASE Statement so that the Sort uses DateSort too - always ascending.
March 28, 2014 at 12:36 pm
This screams of a catch all query. Performance for this type of thing is likely to seriously stink unless you handle it correctly. Take a look at Gail's blog post here. It talks about how to build this type of query and make it fast.
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 1:13 pm
This should get you there...
Create
ProcdynaSort -- exec dynaSort 1, 'DESC'
@SortID int,
@SortDirection char(4)
as
If @SortID = 1 and @SortDirection = 'ASC'
Begin
--Put your query and correct sort here
End
If @SortID = 1 and @SortDirection = 'DESC'
Begin
--Put your query and correct sort here
End
March 28, 2014 at 1:26 pm
Bob McClellan-320407 (3/28/2014)
This should get you there...Create
ProcdynaSort -- exec dynaSort 1, 'DESC'
@SortID int,
@SortDirection char(4)
as
If @SortID = 1 and @SortDirection = 'ASC'
Begin
--Put your query and correct sort here
End
If @SortID = 1 and @SortDirection = 'DESC'
Begin
--Put your query and correct sort here
End
You might also want to take a look at the catch all query post I suggested. This exact style of thing is discussed in there and demonstrates the performance issues of this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 2:07 pm
Thanks Sean... I did check it out.... I typically stay away from dynamic sql when I can.
March 28, 2014 at 2:44 pm
I have done that sort of thing before. I might have had, say, 6 various sort conditions to satisfy and I have written as you suggested. So, effectively you write the whole query 6 times but with different sort conditions.
I have instinctively veered away from this as it seems to go against what seems to be generally regarded as good coding practice - but you are saying it is okay?
Does it get over the various matters discussed in 'Gail's catch-all query' link (in the post above)? I.e. is a plan written for each of the 6 ways of executing the procedure so that the most efficient method is used?
March 28, 2014 at 2:50 pm
But, isn't that not a 'catch-all query'? It hasn't got any ... WHERE (ProjectID = @ProjectID or ProjectID = 0) AND (ContractType = @ContractType or @ContractType = '') ... etc.
It might have
IF @SortOrder = 1
BEGIN
SELECT *
FROM tblProject
WHERE (ProjectID = @ProjectID OR @ProjectID = 0)
ORDER BY Project
END
And, perhaps
IF @SortOrder = 2
BEGIN
SELECT *
FROM tblProject
WHERE (ContractType = @ContractTy OR @ContractType = '')
ORDER BY ContractType
END
March 28, 2014 at 3:04 pm
sku370870 (3/28/2014)
But, isn't that not a 'catch-all query'? It hasn't got any ... WHERE (ProjectID = @ProjectID or ProjectID = 0) AND (ContractType = @ContractType or @ContractType = '') ... etc.It might have
IF @SortOrder = 1
BEGIN
SELECT *
FROM tblProject
WHERE (ProjectID = @ProjectID OR @ProjectID = 0)
ORDER BY Project
END
And, perhaps
IF @SortOrder = 2
BEGIN
SELECT *
FROM tblProject
WHERE (ContractType = @ContractTy OR @ContractType = '')
ORDER BY ContractType
END
There is honestly no way to because all we saw was the ORDER BY. I surmised that it is a type of catch all query because they are sorting based on a parameter. That smells very strongly of a catchall.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 3:07 pm
sku370870 (3/28/2014)
I have done that sort of thing before. I might have had, say, 6 various sort conditions to satisfy and I have written as you suggested. So, effectively you write the whole query 6 times but with different sort conditions.I have instinctively veered away from this as it seems to go against what seems to be generally regarded as good coding practice - but you are saying it is okay?
Does it get over the various matters discussed in 'Gail's catch-all query' link (in the post above)? I.e. is a plan written for each of the 6 ways of executing the procedure so that the most efficient method is used?
Read her article. It explains this in great detail.
You would be better off writing one "driver" proc and a separate proc for each of the 6 paths instead of having multiple paths. Gail has an article on that exact topic too. 😉 http://sqlinthewild.co.za/index.php/2009/09/15/multiple-execution-paths/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 28, 2014 at 3:09 pm
Bob McClellan-320407 (3/28/2014)
Thanks Sean... I did check it out.... I typically stay away from dynamic sql when I can.
Not a bad rule of thumb but as you can see where for a catch all type query it really is the best choice. Just make sure you properly parameterize your dynamic to avoid injection.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply