October 29, 2012 at 12:12 pm
Hi All,
I am trying to build a procedure for a reporting project that would give the end user maximum flexibility when setting parameters and retrieving data.
I want the user to set the number of records returned using a @Top parameter. I also want the user to set the location of the list (top or bottom) from where the specified number of records should be retrieved.
Works just fine in the code below.
I am stuck on how to give the user the option to retrieve all records.
I could do this.
IF @SelectType = 'All' BEGIN SET @Top = 1000000 END
--1000000 is an arbitrary number that I expect to be larger than the maximum number of records returned.
This works but is not very pleasing to me. Ideally in a an 'all' scenario the top and order by statements would be bypassed to improve performance.
Any thoughts?
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestTable]') AND type in (N'U'))
DROP TABLE [dbo].[TestTable]
CREATE TABLE [dbo].[TestTable] (TestData int)
INSERT [dbo].[TestTable] (TestData) VALUES (1)
INSERT [dbo].[TestTable] (TestData) VALUES (2)
INSERT [dbo].[TestTable] (TestData) VALUES (3)
INSERT [dbo].[TestTable] (TestData) VALUES (4)
INSERT [dbo].[TestTable] (TestData) VALUES (5)
INSERT [dbo].[TestTable] (TestData) VALUES (6)
INSERT [dbo].[TestTable] (TestData) VALUES (7)
INSERT [dbo].[TestTable] (TestData) VALUES (8)
INSERT [dbo].[TestTable] (TestData) VALUES (9)
INSERT [dbo].[TestTable] (TestData) VALUES (10)
INSERT [dbo].[TestTable] (TestData) VALUES (11)
INSERT [dbo].[TestTable] (TestData) VALUES (12)
DECLARE @Top int --The number of records to return
DECLARE @SelectType varchar(6) --Either Top, Bottom or ALL
SET @Top = 5
SET @SelectType = 'Top'
--SET @SelectType = 'Bottom'
SELECT
TOP (@Top) TestData
FROM [dbo].[TestTable]
ORDER BY --Sort dynamically by using the @Order parameter
CASE
WHEN @SelectType = 'Bottom' THEN TestData
WHEN @SelectType = 'Top' THEN 1 - TestData
END
October 29, 2012 at 12:34 pm
Easiest way
DECLARE @Top int --The number of records to return
DECLARE @SelectType varchar(6) --Either Top, Bottom or ALL
SET @Top = 5
SET @SelectType = 'ALL'
--SET @SelectType = 'Bottom'
IF @SelectType = 'ALL'
SELECT
TestData
FROM [dbo].[TestTable]
ORDER BY TestData
ELSE
SELECT
TOP (@Top) TestData
FROM [dbo].[TestTable]
ORDER BY --Sort dynamically by using the @Order parameter
CASE
WHEN @SelectType = 'Bottom' THEN TestData
WHEN @SelectType = 'Top' THEN 1 - TestData END
October 29, 2012 at 12:42 pm
Sure and I may go in that direction depending if there are other options.
Downside to forking the code is my actual situation is a bit more complicated and there would be a lot of duplicated code which I would like to avoid if possible...
October 29, 2012 at 12:58 pm
Another solution is dynamic SQL. Write the query to return all rows, and then insert your TOP () and ORDER BY as needed.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 29, 2012 at 1:01 pm
The Dixie Flatline (10/29/2012)
Another solution is dynamic SQL. Write the query to return all rows, and then insert your TOP () and ORDER BY as needed.
I was going to suggest using dynamic sql too. If you go this route make certain you understand sql injection and how to parameterize dynamic sql. Gail has a great example in her article about catch all queries. http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/url]. I suspect you could probably glean some additional insight to your specific challenge from that article too. It is not an exact match but many of the concepts are closely related to what you are doing here.
_______________________________________________________________
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/
October 29, 2012 at 1:02 pm
Try the setting the Rowcount.
DECLARE @t INT
SET @t = 1
SET ROWCOUNT @t
SELECT *
FROM MyTable
DECLARE @t INT
SET @t = 220
SET ROWCOUNT @t
SELECT *
FROM MyTable
October 29, 2012 at 1:10 pm
Hope this is what you are looking for:
DECLARE @Top int --The number of records to return
DECLARE @SelectType varchar(6) --Either Top, Bottom or ALL
--SET @Top = 5
SET @SelectType = 'Top'
--SET @SelectType = 'Bottom'
SELECT
TOP (isnull(@Top,1000000)) TestData
FROM [dbo].[TestTable]
ORDER BY --Sort dynamically by using the @Order parameter
CASE
WHEN @SelectType = 'Bottom' THEN TestData
WHEN @SelectType = 'Top' THEN 1 - TestData
END
October 29, 2012 at 1:16 pm
Ray M (10/29/2012)
Try the setting the Rowcount.
DECLARE @t INTSET @t = 1
SET ROWCOUNT @t
SELECT *
FROM MyTable
DECLARE @t INT
SET @t = 220
SET ROWCOUNT @t
SELECT *
FROM MyTable
Not the approach I would take but if you use this option you still need an ORDER BY.
_______________________________________________________________
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/
October 29, 2012 at 1:25 pm
You could just use a CTE with a row number and order asc or desc based on "top" or "bottom". This would also give you the flexibility of returning a middle range, rows 500 to 1000 for example. Not sure how you would do that with a top.
October 29, 2012 at 3:17 pm
Thanks All,
My option seem clear.
1) dynamic sql
2) The easy and one could argue proper way, IF @SelectType = 'ALL' ...ELSE...
3) My mini hack-SET top to an arbitrary maximum number
I may have to do so some testing to see if the performance hit involved in 3 is worth it.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply