checking for empty strings (VARCHAR)

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)

checking for empty strings (VARCHAR)

Author

Topic

gobotsoup
Starting Member

6 Posts

Posted - 2007-03-29 : 17:45:00

Hello I am tring to check for empty strings (for variable:Location)to prevent bad concurency issues when updating a row in a table. The syntax I am using doesn't seem to be working (row is not being updated when Location=''). Can anyone identify what the problem in the code below is? Thanks for your help!

@Location VARCHAR(50),
@original_Location VARCHAR(50)
UPDATE HardwareAssets SET  [Location] = @Location
	WHERE 
([Location]='' AND @original_Location='')
     OR
 ([Location] = @original_Location)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 17:55:51

Are NULL values an issue here?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 17:58:07

[code]UPDATE HardwareAssets
SET Location = @Location
WHERE COALESCE(Location, '') = COALESCE(@Original_Location, '')[/code]

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-29 : 17:58:09

When Location = '' is @original_Location always = '' too? That's what your code is looking for. If you want to update Location when it is = '' or when it is = @original_Location then your WHERE clause should be

WHERE [Location] = '' OR [Location] = @original_Location

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 18:06:07

[code]UPDATE HardwareAssets
SET Location = @Location
WHERE COALESCE(Location, '') IN ('', COALESCE(@Original_Location, ''))

UPDATE HardwareAssets
SET Location = @Location
WHERE Location IN ('', @Original_Location)[/code]

Peter Larsson
Helsingborg, Sweden

gobotsoup
Starting Member

6 Posts

Posted - 2007-03-29 : 18:39:18

Thanks Peso (Peter)the

Location IN ('', @Original_Location)]

Did the trick!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-29 : 19:01:10

That's exactly what snSQL wrote too, with other syntax.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources