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

update statement with isnull and nullif functions. Expand / Collapse
Author
Message
Posted Wednesday, May 12, 2010 4:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, October 22, 2013 2:34 AM
Points: 17, Visits: 280
1.SP_ Template
@Code char(30),
@Path varchar (50),
@PathReadOnlyInd int

The SP must update table MyTable
If any one of the parameters @Path or @PathReadOnlyInd are null or blank the value currently in the table must remain unchanged. Use one update statement with isnull and nullif functions.

CREATE PROCEDURE dbo.SP_Template
@Code char(30),
@Path varchar(100) = null,
@PathReadOnly int = null

AS

BEGIN
UPDATE dbo.Mytable
SET
Path = isnull(nullif(@Path,''),'')
PathReadOnly = isnull(nullif(@PathReadOnly,''),'')
FROM dbo. Mytable
WHERE Code = @Code
END

But this will overwrite the current values with blanks.
Post #920305
Posted Wednesday, May 12, 2010 4:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 4:03 AM
Points: 50, Visits: 342
Hi,

I believe this should work, but it is untested because you provided no sample data. :)

CREATE PROCEDURE dbo.SP_Template
@Code char(30),
@Path varchar(100) = null,
@PathReadOnly int = null

AS
BEGIN

--set @path = null if it is blank
IF LEN(LTRIM(RTRIM(ISNULL(@path,''))) = 0
SET @path = null

UPDATE m
SET
Path = ISNULL(@Path,m.path),
PathReadOnly = ISNULL(@PathReadOnly,m.PathReadOnly)
FROM dbo.Mytable as m
WHERE Code = @Code

END

The isnull() will return the value from your table if @path/@PathReadOnly is null.

Steffen.
Post #920312
Posted Wednesday, May 12, 2010 4:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:09 AM
Points: 2,873, Visits: 5,183
As being minimalist, the following single line will also work to set your param to null if it is a blank (or spaces) without any trims and checks for length (it would also work if your @path param would be of CHAR datatype):

SET @path = NULLIF(@path,'')

Actually, you don't need even that, just use:

UPDATE m 
SET
Path = ISNULL(@Path,NULLIF(m.path,'')), -- don't update when no path supplied
PathReadOnly = ISNULL(@PathReadOnly,m.PathReadOnly)
FROM dbo.Mytable as m
WHERE Code = @Code



_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #920321
Posted Wednesday, May 12, 2010 4:59 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, October 9, 2014 4:03 AM
Points: 50, Visits: 342
Shouldn't the NULLIF be around the @Path variable?

UPDATE m 
SET
Path = ISNULL(NULLIF(@Path,''),m.path), -- don't update when no path supplied
PathReadOnly = ISNULL(@PathReadOnly,m.PathReadOnly)
FROM dbo.Mytable as m
WHERE Code = @Code

I did never use NULLIF before, but it looks really useful. ;)

Steffen.
Post #920330
Posted Wednesday, May 12, 2010 5:10 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, October 10, 2014 2:09 AM
Points: 2,873, Visits: 5,183
Just another small advice:

Don't use stored proc input parameters directly in the WHERE clause of query, copy them into the local variables first and use the local variables instead.
It will prevent performance problems due to parameter sniffing.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #920334
Posted Wednesday, May 12, 2010 5:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 9, 2014 7:34 AM
Points: 2,666, Visits: 4,736
You can also use a CASE statement for the same

UPDATE	dbo.Mytable
SET @Path = COALESCE( @Path, '' ),
@PathReadOnly = COALESCE( @PathReadOnly, '' ) ,
Path = CASE WHEN @Path = '' THEN Path ELSE @Path END,
PathReadOnly = CASE WHEN @PathReadOnly = '' THEN PathReadOnly ELSE @PathReadOnly END
WHERE Code = @Code


Looks longer but easy to read and understand



Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #920349
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse