update statement with isnull and nullif functions.

Question

Post reply

update statement with isnull and nullif functions.

Maina-456685

Old Hand

Points: 389
More actions
May 12, 2010 at 4:00 am

#224413

:-P1.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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

shadow_2 Right there with Babe Points: 773 More actions · Answer 1

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.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

shadow_2 Right there with Babe Points: 773 More actions · Answer 3

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.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

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!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 5

You can also use a CASE statement for the same

UPDATEdbo.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

WHERECode = @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/