Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Null Value Handling - Stored Procedure Expand / Collapse
Author
Message
Posted Monday, March 24, 2008 3:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 9, 2011 9:02 PM
Points: 25, Visits: 90
I'm writing an script that will create a table and run a stored procedure to populate the table with data.
It works fine, but the vendor who will eventually import my flat file says to me that he can't receive any column values = 'NULL'.
In the case of my database, it is unavoidable but I was wondering if anyone knew how to update my set statements with some
sort of "else if" clause for inserting a true "" (blank) value if the column contains a NULL value.

Here is the script:


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotel_export]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[hotel_export]
GO

CREATE TABLE [dbo].[hotel_export] (
[room_code] [varchar] (15) NOT NULL ,
[guest_lname] [varchar] (50) NULL ,
[guest_fname] [varchar] (50) NULL ,
[freq_guest_num] [varchar] (30) NULL ,
[addr1] [varchar] (100) NULL ,
[addr2] [varchar] (100) NULL ,
[city] [varchar] (50) NULL ,
[state] [varchar] (40) NULL ,
[zip] [varchar] (20) NULL ,
[country] [varchar] (30) NULL ,
[company_name] [varchar] (100) NULL ,
[email] [varchar] (200) NULL ,
[adults_num] [varchar] (20) NULL ,
[room_type] [varchar] (50) NULL ,
[crs] [varchar] (12) NULL ,
[phone] [varchar] (40) NULL ,
[share1_lname] [varchar] (50) NULL ,
[share1_fname] [varchar] (50) NULL ,
[share2_lname] [varchar] (50) NULL ,
[share2_fname] [varchar] (50) NULL ,
[share3_lname] [varchar] (50) NULL ,
[share3_fname] [varchar] (50) NULL ,
[m_message] [varchar] (20) NULL ,
[rand] [varchar] (8) NULL ,
[shareacct] [varchar] (10) NULL ,
[account] [varchar] (12) NULL ,
[id] [varchar] (30) NULL,
[date_from] [varchar] (20) NULL ,
[date_to] [varchar] (20) NULL ,
[cxl_acct] [varchar] (14) NULL ,
[special1] [varchar] (6) NULL ,
[status] [varchar] (1) NULL ,
[status_desc] [varchar] (8) NULL ,
[arr_time] [varchar] (8) NULL ,
[add_type] [varchar] (8) NULL ,
[group_id] [varchar] (8) NULL ,
[group_name] [varchar] (8) NULL ,
[child] [varchar] (2) NULL ,
[honor] [varchar] (8) NULL
) ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[hotel_export_proc]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[hotel_export_proc]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE hotel_export_proc AS

DECLARE @outoforder varchar (12)
DECLARE @ooofrom varchar (20)
DECLARE @oooto varchar (20)
DECLARE @room_code varchar (6)
DECLARE @guest_name varchar (70)
DECLARE @guest_fname varchar (1)
DECLARE @account varchar(14)
DECLARE @date_from varchar (20)
DECLARE @date_to varchar (20)
DECLARE @freq_guest_num varchar (12)
DECLARE @addr1 varchar (40)
DECLARE @addr2 varchar (40)
DECLARE @city varchar (20)
DECLARE @state varchar (6)
DECLARE @zip varchar (10)
DECLARE @country varchar (6)
DECLARE @company_name varchar (70)
DECLARE @adults_num varchar (2)
DECLARE @phone varchar (15)
DECLARE @special1 varchar (6)
DECLARE @special2 varchar (6)
DECLARE @special3 varchar (6)
DECLARE @special4 varchar (6)
DECLARE @special5 varchar (6)
DECLARE @room_type varchar (6)
DECLARE @out_time varchar (20)
DECLARE @share1_lname varchar (50)
DECLARE @share1_fname varchar (50)
DECLARE @share2_lname varchar (50)
DECLARE @share2_fname varchar (50)
DECLARE @share3_lname varchar (50)
DECLARE @share3_fname varchar (50)
DECLARE @email varchar (40)
DECLARE @shareacct varchar (12)
DECLARE @checked_out varchar (20)
DECLARE @crs varchar (12)
DECLARE @m_message varchar (20)
DECLARE @rand varchar (8)
DECLARE @id varchar (30)
DECLARE @cxl_acct varchar (14)
DECLARE @status varchar (1)
DECLARE @status_desc varchar (8)
DECLARE @arr_time varchar (8)
DECLARE @add_type varchar (8)
DECLARE @group_id varchar (8)
DECLARE @group_name varchar (8)
DECLARE @child varchar (2)
DECLARE @honor varchar (8)

declare curse cursor
for select code from z_rooms where property='DELSOL'

open curse
fetch next from curse into @room_code
while (@@fetch_status<>-1)
begin
if (@@fetch_status<>-2)
begin



set @account=(select TOP 1 min(g.account) from guest g, z_property z where (g.room=@room_code and g.status='I') or (g.room=@room_code and g.status='O' and CONVERT (datetime,departure,3)+ CAST (out_time as datetime) >=Dateadd(mi,-5,GetDate())) group by account)
set @guest_name=isnull(isnull((select name from guest where (room=@room_code and account=@account and status='I')or (room=@room_code and account=@account and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) ),@outoforder) ,'VACANT')
set @guest_fname=isnull(isnull((select name from guest where (room=@room_code and account=@account and status='I')or (room=@room_code and account=@account and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) ),@outoforder) ,'VACANT')
set @date_from=isnull((select CONVERT (varchar,arrival,3) from guest where (account=@account and room=@room_code and status='I') or (account=@account and room=@room_code and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) group by arrival),@ooofrom)
set @date_to=isnull((select CONVERT (varchar,departure,3) from guest where (account=@account and room=@room_code and status='I') or (account=@account and room=@room_code and status='O' and @out_time >= DateAdd(mi, -5, GetDate()) ) group by departure),@oooto)
set @freq_guest_num=( select ghacct from guest where account=@account and room=@room_code group by ghacct)
set @addr1=(select address from guest where account=@account and room=@room_code group by address)
set @addr2=(select address2 from guest where account=@account and room=@room_code group by address2)
set @city=(select city from guest where account=@account and room=@room_code group by city)
set @state=(select state from guest where account=@account and room=@room_code group by state)
set @zip=(select zip from guest where account=@account and room=@room_code group by zip)
set @country=(select country from guest where account=@account and room=@room_code group by country)
set @company_name=(select company from guest where account=@account and room=@room_code group by company)
set @adults_num=(select adult from guest where account=@account and room=@room_code group by adult)
set @phone=(select phone from guest where account=@account and room=@room_code group by phone)
set @room_type=(select roomtype from z_rooms where code=@room_code group by roomtype)
set @email=(select email from guest where account=@account and room=@room_code group by email)
set @share1_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account)),'')
set @share2_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_lname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_lname)),'')
set @share3_lname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_lname and g.name<>@share2_lname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_lname and n.name<>@share2_lname)),'')
set @share1_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account)),'')
set @share2_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_fname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_fname)),'')
set @share3_fname=isnull(isnull((select min(g.name) from guest g where g.status='I' and g.room=@room_code and account<>@account and g.name<>@share1_fname and g.name<>@share2_fname),(select min(n.name) from guest_names n,guest g where g.status='I' and n.account=g.account and
g.room=@room_code and n.preferred_name<>'Y' and n.account=@account and n.name<>@share1_fname and n.name<>@share2_fname)),'')
set @special1=(select TOP 1 min(s.special) from guest_specials s,z_property z where s.account=@account and s.status='A' and z.hotel_date between s.from_date and s.to_date group by s.special)
set @shareacct=( select shareacct from guest where account=@account and room=@room_code group by shareacct)
set @crs=( select crs from guest where account=@account and room=@room_code group by crs)
set @m_message=(select m_message from guest where account=@account and room=@room_code group by m_message)
set @cxl_acct=( select cxl_acct from guest where account=@account and room=@room_code group by cxl_acct)
set @arr_time=( select arr_time from guest where account=@account and room=@room_code group by arr_time)
set @child=( select child from guest where account=@account and room=@room_code group by child)
INSERT INTO hotel_expert_room_registry (room_code,guest_lname,guest_fname,freq_guest_num,addr1,addr2,city,state,zip,country,company_name,email,adults_num,room_type,crs,phone,share1_lname,share1_fname,share2_lname,share2_fname,share3_lname,share3_fname,m_message,rand,shareacct,account,id,date_from,date_to,cxl_acct,special1,status,status_desc,arr_time,add_type,group_id,group_name,child,honor)
VALUES (@room_code, @guest_name,@guest_fname,@freq_guest_num,@addr1,@addr2,@city,@state,@zip,@country,@company_name,@email,@adults_num,@room_type,@crs,@phone,@share1_lname,@share1_fname,@share2_lname,@share2_fname,@share3_lname,@share3_fname,@m_message,@rand,@shareacct,@account,@id,@date_from,@date_to,@cxl_acct,@special1,@status,@status_desc,@arr_time,@add_type,@group_id,@group_name,@child,@honor)

end
fetch next from curse into @room_code
end
close curse
deallocate curse
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Post #473735
Posted Monday, March 24, 2008 3:08 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 4:05 PM
Points: 2,717, Visits: 3,835
You're already doing it in some other cases, like the quest_name line ...

but anyway ...

set @phone=ISNULL((select phone from guest where account=@account and room=@room_code group by phone),'')



______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Post #473740
Posted Monday, March 24, 2008 3:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 9, 2011 9:02 PM
Points: 25, Visits: 90
thank you...you're totally right...i did not know that function did that and bc the data in the columns already using that function did not have any "blanks" (nulls) it kind of threw me.
Post #473747
Posted Monday, March 24, 2008 4:04 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 9, 2011 9:02 PM
Points: 25, Visits: 90
Jason Selburg (3/24/2008)
You're already doing it in some other cases, like the quest_name line ...

but anyway ...

set @phone=ISNULL((select phone from guest where account=@account and room=@room_code group by phone),'')



could you verify how to form this particular set statement using the isnull function?

set @special1=isnull(isnull((select TOP 1 min(s.special) from guest_specials s,z_property z where s.account=@account and s.status='A' and z.hotel_date between s.from_date and s.to_date group by s.special)),'')


when i use this...i get the error: The isnull function requires 2 arguments.

thanks for your help
Post #473767
Posted Monday, March 24, 2008 4:14 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
ISNULL does require two arguments and you have 2 ISNULL functions in that statement (only the outer one has the second argument). I suspect that you do not need the second ISNULL.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #473768
Posted Monday, March 24, 2008 4:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, December 9, 2011 9:02 PM
Points: 25, Visits: 90
rbarryyoung (3/24/2008)
ISNULL does require two arguments and you have 2 ISNULL functions in that statement (only the outer one has the second argument). I suspect that you do not need the second ISNULL.


awesome yea...this is what i left it at...i didn't need the additional function...


set @special1=isnull((select TOP 1 min(s.special) from guest_specials s,z_property z where s.account=@account and s.status='A' and z.hotel_date between s.from_date and s.to_date group by s.special),'')
Post #473782
Posted Monday, March 24, 2008 10:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
Let's see if I counted this right...

1 Cursor
30 separate SELECTs (many nearly identical) not including the cursor
1 Insert

... per row...

I wonder how long this puppy is going to take to insert several thousand rows compared to a set based solution... ;)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #473864
Posted Wednesday, March 26, 2008 11:03 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 15, 2013 3:14 AM
Points: 247, Visits: 215
Did anyone else notice what he called "CURSE"



Post #474935
Posted Wednesday, March 26, 2008 12:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:51 PM
Points: 36,959, Visits: 31,469
Heh, yeaup... thought I'd let that one go :P

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #474991
Posted Wednesday, March 26, 2008 12:19 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:10 PM
Points: 7,135, Visits: 15,152
Actually - it's one of my favored names for those....:D

'cause that's usually what I end up doing when I find them.....


----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Post #474995
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse