Return Certain Number of Rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Return Certain Number of Rows

Author

Topic

stormcandi
Starting Member

46 Posts

Posted - 2008-04-17 : 00:18:07

I have a Dataset that I am populating from a SQL Query. I am then using the dataset to populate a report in Reporting Services. What I want to do is return a standard number of rows in my dataset. (Let's say 10.) Even if my query does not have any rows in it, I want 10 empty rows returned to the dataset. If my query has 7 rows in it then I want to add on 3 empty rows and return it. I will not have more than the standard number of rows.
I cannot get the table in the report to show up if the dataset is empty, but still want the table to display with 10 empty rows. I have searched how to do this online but am getting nowhere. (I know how to add one empty row but not a set number.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-17 : 00:28:06

What is the need for displaying empty rows in your report?

stormcandi
Starting Member

46 Posts

Posted - 2008-04-17 : 00:32:39

quote:
Originally posted by visakh16

What is the need for displaying empty rows in your report?

The reason why is because I need to allow entry fields for the report so when it is printed there are empty fields for the user to enter information in.

bfoster
Starting Member

30 Posts

Posted - 2008-04-17 : 08:47:24

As long as the data table has more than 10 rows total this could be one way to approach the problem. If this won't work for you, try to be more specific about your requirements.

DECLARE @Count INT

SELECT @Count = CASE WHEN COUNT(*) > 10 THEN 0 ELSE 10 - COUNT(*) END
FROM MyData
WHERE MyCondition = whatever

SELECT Column1, Column2, Column3
FROM MyData
WHERE MyCondition = whatever
UNION ALL
SELECT TOP(@Count) NULL, NULL, NULL
FROM MyData

stormcandi
Starting Member

46 Posts

Posted - 2008-04-21 : 17:52:34

quote:
Originally posted by bfoster

As long as the data table has more than 10 rows total this could be one way to approach the problem. If this won't work for you, try to be more specific about your requirements.

DECLARE @Count INT

SELECT @Count = CASE WHEN COUNT(*) > 10 THEN 0 ELSE 10 - COUNT(*) END
FROM MyData
WHERE MyCondition = whatever

SELECT Column1, Column2, Column3
FROM MyData
WHERE MyCondition = whatever
UNION ALL
SELECT TOP(@Count) NULL, NULL, NULL
FROM MyData

I have tried this in this query:
Select @Count = CASE WHEN COUNT(*) > 10 THEN 0 ELSE 10 - COUNT(*) END
From Plan Inner Join Course ON Plan.CourseID = Course.CID
Where ID IN (@ID)
And Plan.Grade = 10

Select (Plan.ID + ' ' + CN) as Course,
From Plan Inner Join Course ON Plan.CourseID = Course.CID
Where ID IN (@ID)
And Plan.Grade = 10
UNION ALL
Select Top(@Count) null
From Plan

But Sql Server doesn't like my Select Top(@Count) null From Plan portion of the query. Can you see if I am overlooking something?

Greg Larsen
Starting Member

1 Post

Posted - 2008-04-21 : 21:10:26

I think this might get what you want.

set nocount on
create table x (id char(1))
insert into x values('A')
insert into x values('B')
insert into x values('C')

create table dummy (id char(1))
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)

declare @count int
select COUNT(*) from x
Select @Count = CASE WHEN COUNT(*) > 10 THEN 0 ELSE 10 - COUNT(*) END
From x

print @count
Select id from x
UNION ALL
Select Top(@Count) id
From dummy

drop table x, dummy

For more sql server examples check out http://sqlserverexamples.com

stormcandi
Starting Member

46 Posts

Posted - 2008-04-23 : 14:59:21

That worked wonderfully. Is what I ended up doing is Setting the Row Count to make sure that I only received 10 rows back.
Thanks a bunch!

quote:
Originally posted by Greg Larsen

I think this might get what you want.

set nocount on
create table x (id char(1))
insert into x values('A')
insert into x values('B')
insert into x values('C')

create table dummy (id char(1))
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)
insert into dummy values(null)

declare @count int
select COUNT(*) from x
Select @Count = CASE WHEN COUNT(*) > 10 THEN 0 ELSE 10 - COUNT(*) END
From x

print @count
Select id from x
UNION ALL
Select Top(@Count) id
From dummy

drop table x, dummy

For more sql server examples check out http://sqlserverexamples.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources