Forcing a set number of result rows in a query

I'm trying to select 5 rows of data from a query. Sometimes there is less than 5 rows of data in the result set.

Is there a way to FORCE a return of 5 rows - even if they don't exist? For example, returning some text such as "No Data" or NULL in the result set?

What I'm doing to return 5 rows of data:

Select top 5 *

From MyTable

I need help modifying this query to make sure I always get 5 rows of data.

Thanks!

There is no pre-defined settings available but you do something below,

Code Snippet

Create table #Data(

Id int,

Name varchar(100)

)

Insert Into #Data Values(1,100)

Insert Into #Data Values(2,100)

Insert Into #Data Values(3,100)

Select Top 5 * From

(

Select Id, Name from #Data

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

)

as Data

Order By Case When Id is NULL Then 1 Else 0 End , ID

|||

Code Snippet

CREATE TABLE #temp (test int)

INSERT INTO #temp SELECT 1

INSERT INTO #temp SELECT 2

INSERT INTO #temp SELECT 3

DECLARE @.counter as int

set @.counter = (SELECT COUNT(*) from #temp)

SELECT * FROM #temp

WHILE @.counter < 5

BEGIN

INSERT INTO #temp SELECT NULL

SET @.counter = @.counter + 1

END

SELECT * FROM #temp

DROP TABLE #temp

Adamus

|||Thanks for the prompt replies - both of these replies were helpful and answered my question!