SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Null Value Handling - Stored Procedure


Null Value Handling - Stored Procedure

Author
Message
jonathanmreynolds
jonathanmreynolds
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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

Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6924 Visits: 4115
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
jonathanmreynolds
jonathanmreynolds
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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.
jonathanmreynolds
jonathanmreynolds
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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
RBarryYoung
RBarryYoung
SSC-Dedicated
SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

Group: General Forum Members
Points: 34938 Visits: 9518
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."
jonathanmreynolds
jonathanmreynolds
SSC-Enthusiastic
SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)SSC-Enthusiastic (125 reputation)

Group: General Forum Members
Points: 125 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),'')

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214852 Visits: 41979
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... Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Neuro
Neuro
Old Hand
Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)Old Hand (383 reputation)

Group: General Forum Members
Points: 383 Visits: 239
Did anyone else notice what he called "CURSE"
w00t



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214852 Visits: 41979
Heh, yeaup... thought I'd let that one go Tongue

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Matt Miller (4)
Matt Miller (4)
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29439 Visits: 19002
Actually - it's one of my favored names for those....BigGrin

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

----------------------------------------------------------------------------------
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search