Click here to Skip to main content
15,887,175 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
SQL
ALTER PROCEDURE [dbo].[FETCH_MENU_DETAILS_TOREPORT_SAMPLE]
(
@COMPANY_NAME NVARCHAR(250),
@FROM_DATE DATETIME
)
AS
BEGIN
DECLARE @BOOKEDID INT
DECLARE @COMPANYNAME1 NVARCHAR(450)
DECLARE @NOOFDAYS BIGINT
DECLARE @ARRIVAL_DATE DATETIME

SELECT @BOOKEDID= hall_id,@COMPANYNAME1=@COMPANY_NAME,@NOOFDAYS=no_of_days,@ARRIVAL_DATE=@FROM_DATE from hall_booked_details where company_name=@COMPANY_NAME and from_date=@FROM_DATE

DECLARE @BREAK_ITEMS TABLE 
(
ITEMS NVARCHAR(4000)
)

INSERT INTO @BREAK_ITEMS
select dbo.ConvertMenuName(menu_items_id) from MENU_ITEMS_CHOOSED_HALLBOOKED_CUSTOMERS where hall_booked_id=@BOOKEDID and menu_id=1

DECLARE @LUNCH_ITEMS TABLE
(
ITEMS NVARCHAR(4000)
)
INSERT INTO @LUNCH_ITEMS
select dbo.ConvertMenuName(menu_items_id) from MENU_ITEMS_CHOOSED_HALLBOOKED_CUSTOMERS where hall_booked_id=@BOOKEDID and menu_id=2

DECLARE @DINNER_ITEMS TABLE
(
ITEMS NVARCHAR(4000)
)
INSERT INTO @DINNER_ITEMS
select dbo.ConvertMenuName(menu_items_id) from MENU_ITEMS_CHOOSED_HALLBOOKED_CUSTOMERS where hall_booked_id=@BOOKEDID and menu_id=3

DECLARE @TOTAL_ITEMS TABLE
(
COMPANY_NAME NVARCHAR(450),
TOTAL_DAYS BIGINT,
ARRIVAL_DATE DATETIME,
BREAK_FAST_ITEMS NVARCHAR(4000),
LUNCH_ITEMS NVARCHAR(4000),
DINNER_ITEMS NVARCHAR(4000)
)
INSERT INTO @TOTAL_ITEMS
SELECT @COMPANYNAME1,@NOOFDAYS,@ARRIVAL_DATE,*,null,null FROM @BREAK_ITEMS

INSERT INTO @TOTAL_ITEMS
SELECT @COMPANYNAME1,@NOOFDAYS,@ARRIVAL_DATE,null,*,null FROM @LUNCH_ITEMS


INSERT INTO @TOTAL_ITEMS
SELECT @COMPANYNAME1,@NOOFDAYS,@ARRIVAL_DATE,null,*,null FROM @DINNER_ITEMS


declare @sp table
(
company_name varchar(350),
totaldays bigint,
arrivaldate datetime,
breakfast nvarchar(4000),
lunch nvarchar(4000),
dinner nvarchar(4000)
)
insert into @sp
select * from @TOTAL_ITEMS

select * from @sp


END


i am getting output like this
C1  C2    C3                   C4                    C5        C6
------------------------------------------------------------
ABC 2   2012-10-24  brinjal,              NULL       NULL   
ABC 2   2012-10-24  apple salad,          NULL       NULL   
ABC 2   2012-10-24  egg bread,            NULL       NULL   
ABC 2   2012-10-24  leg pieces,           NULL       NULL   
ABC 2   2012-10-24  briyani,              NULL       NULL   
ABC 2   2012-10-24  NULL                  nilepix,   NULL
ABC 2   2012-10-24  NULL                  NULL       NULL   
ABC 2   2012-10-24  NULL                 fruit rice, NULL
ABC 2   2012-10-24  NULL                 strawberry, NULL
ABC 2   2012-10-24  NULL                 fried rice, NULL



i want actual output like 
C1  C2    C3                   C4                    C5        C6
------------------------------------------------------------
ABC 2   2012-10-24  brinjal,              nilepix,       
ABC 2   2012-10-24  apple salad,          rice,
ABC 2   2012-10-24  egg bread,            fruit rice,
ABC 2   2012-10-24  leg pieces,           strawberry,
ABC 2   2012-10-24  briyani,              fried rice
Posted
Updated 21-Nov-12 22:59pm
v4
Comments
Sergey Alexandrovich Kryukov 22-Nov-12 4:49am    
Curious why? What's wrong with null values?
--SA
Umapathi K 22-Nov-12 5:11am    
i am using this to report., in report it occurs space thats why need to avoid null values
Sergey Alexandrovich Kryukov 22-Nov-12 10:46am    
I don't know why and don't understand you approach. What is it, sacrifice something in the base of business, the database structure, in order to beautify mysterious report layouts? This will lead you nowhere...
--SA
Fathima, Coimbatore 22-Nov-12 5:01am    
use group by & null functions.
Umapathi K 22-Nov-12 5:11am    
can u more clear

Hi Umaphathi

You can use the ISNULL function of sql server to provide a default value for a column in case the value is null in select statement.

Refer the link http://msdn.microsoft.com/en-us/library/aa933210%28v=sql.80%29.aspx[^]

Regards
Pawan

Note: Please accept the solution if it fulfills your critirea
 
Share this answer
 
I think you write a where clause as

SQL
select column1, column2
from tableName
where column1 is not null 


Milind
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900