[Solved] problem updating row when there is a null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

[Solved] problem updating row when there is a null

Author

Topic

CVDpr
Starting Member

41 Posts

Posted - 2008-09-16 : 17:46:11

Hello there when i do a update....set.. where ServiceArea = @ServiceArea work
but when ServiceArea has a null, ServiceArea = null dont work.

Im currently using this and is working


create procedure sp_upd_x 



@ServiceArea as char(10)


as
begin

declare @err int
	begin transaction	

		 if @ServiceArea is null null
			update x set 			
			
			and ServiceArea is null
			else
				update x set 
			and ServiceArea = @Servicearea					

		set @err = @@ERROR

			if @err = 0 
				 begin
					commit transaction
				 end
			else
				begin
					rollback transaction
					
				end
					
end

Its there another way/better to change the and Service = @ServiceArea to and Service is null when there is a null

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-16 : 18:17:31

Just use COALESCE to handle both.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-17 : 12:00:41

Here are 2 ways to implement that logic:

AND (ServiceArea = @ServiceArea OR ServiceArea IS NULL)

AND COLESCE(ServiceArea, @ServiceArea) = @ServiceArea

EDIT: I was just looking at your logic again and noticed that you are trying to compare NULLs directly.

Maybe this would be more suitable:
AND (ServiceArea = @ServiceArea OR (ServiceArea IS NULL AND ServiceArea IS NULL)

Also, if you know that a blank would not occur normally you could do:
AND COALESCE(ServeriveArea, '') = COALESCE(@ServiceArea, '')

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-17 : 12:06:27

Another alternative is to change the SET ANSI_NULLS setting to OFF. But, it is probalby better to understand that NULL <> NULL while ANSI_NULLS is set to ON (The default).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 12:08:02

[code](
@CompanyId as int,
@FacilityId as int,
@CDMId as int,
@InsID as int,
@ServiceArea as char(10)= null,
@EffectiveDateFrom as smalldatetime,
@CPT as char(5),
@Modifier1 as char(5),
@Modifier2 as char(5),
@Modifier3 as char(5),
@Modifier4 as char(5)
)
as

SET NOCOUNT ON

declare @err int

if @servicearea is null
update AppInformationOfBillingByInsuranceAndServiceArea
set CPT = @CPT,
Modifier1 = @Modifier1,
Modifier2 = @Modifier2,
Modifier3 = @Modifier3,
Modifier4 = @Modifier4
where CompanyId = @CompanyId
and FacilityId = @FacilityId
and CDMId = @CDMId
and InsId = @InsId
and EffectiveDateFrom = @EffectiveDateFrom
else
update AppInformationOfBillingByInsuranceAndServiceArea
set CPT = @CPT,
Modifier1 = @Modifier1,
Modifier2 = @Modifier2,
Modifier3 = @Modifier3,
Modifier4 = @Modifier4
where CompanyId = @CompanyId
and FacilityId = @FacilityId
and CDMId = @CDMId
and InsId = @InsId
and EffectiveDateFrom = @EffectiveDateFrom
and ServiceArea = @ServiceArea

if @@ERROR <> 0
rollback transaction
else
commit transaction[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources