Solved

Force "empty" row return

Posted on 2009-04-15
5
1,156 Views
Last Modified: 2012-05-06
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
Comment
Question by:tbaseflug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 24149524
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 Comment

by:tbaseflug
ID: 24149690
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,

Open in new window

0
 
LVL 11

Expert Comment

by:N R
ID: 24149785
You mean like:

select *
from dbo.cm_tblcontractstoploss
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24149815
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

Open in new window

0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24149913
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

Open in new window

0

Featured Post

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question