如何避免SQL中的空值 [英] how to avoid null values in 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

推荐答案

Umaphathi

如果select语句中的值为null，则可以使用sql server的ISNULL函数为列提供默认值.

请参阅链接 http://msdn.microsoft.com/en-us /library/aa933210%28v=sql.80%29.aspx [

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

我认为您将where子句写为

I think you write a where clause as

select column1, column2
from tableName
where column1 is not null 

Milind

Milind

这篇关于如何避免SQL中的空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！