Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1176

# Force "empty" row return

I need to return 5 rows - if there is data corresponding to all 5 great - would like to return that - if not, say - only data in the table for 2 out of 5 rows - then show the first two populated with data and then 3 empty rows, etc.

Is this even possible in SQL?
0
tbaseflug
1 Solution

Commented:
sure
you can return all nulls using a left outer join
but for me to provide you with the syntax i need your table structure
0

Author Commented:
Here it is
``````CREATE TABLE [dbo].[CM_tblContractStopLoss](
[stopLossID] [int] IDENTITY(1,1) NOT NULL,
[contractID] [int] NOT NULL,
[startDate] [datetime] NULL,
[endDate] [datetime] NULL,
[dollarThreshold] [money] NULL,
[billedChargesPercentage] [decimal](4, 2) NULL,
[dollarType] [tinyint] NULL,
[dollarNotToEceed] [money] NULL,
[patientType] [tinyint] NULL,
``````
0

Founder/CTOCommented:
You mean like:

select *
from dbo.cm_tblcontractstoploss
0

Commented:
you can do something like this

``````declare @sql nvarchar(max)
declare @param varchar(10)
declare @rows int
set @param='mis'
select @rows=count(id) from emps where dept=@param
set @sql='select name from emps where dept='+char(39)+@param+char(39)+ ' union all'
--exec sp_executesql @sql
if @rows<5
begin
declare @i int
set @i=5-@rows
while @i>0
begin
set @sql=@sql+ ' select char(13) as name  union all'
set @i=@i-1
end
end
set @sql=left(@sql,len(@sql)-9)
exec sp_executesql @sql
``````
0

Commented:
here is bit modified version for your own table. however you have to include remaining column, I have used only two column

``````declare @sql nvarchar(max)
declare @param varchar(10)
declare @rows int
select @rows=count(stopLossID) from CM_tblContractStopLoss
set @sql='select stopLossID,contractid from CM_tblContractStopLoss union all'
--exec sp_executesql @sql
if @rows<5
begin
declare @i int
set @i=5-@rows
while @i>0
begin
set @sql=@sql+ ' select NULL as stopLossID,NULL as contractid  union all'
set @i=@i-1
end
end
set @sql=left(@sql,len(@sql)-9)
print @sql
exec sp_executesql @sql
``````
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.