Dynamic TOP query with an option for all records

Question

Post reply

Dynamic TOP query with an option for all records

Chrissy321

SSCoach

Points: 15766
More actions
October 29, 2012 at 12:12 pm

#266275

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

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

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

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Chrissy321 SSCoach Points: 15766 More actions · Answer 2

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...

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

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/

Ray M SSC-Insane Points: 21093 More actions · Answer 5

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

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 6

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

Ray M (10/29/2012)
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

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/

brendan woulfe Hall of Fame Points: 3888 More actions · Answer 8

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.

Chrissy321 SSCoach Points: 15766 More actions · Answer 9

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.