[sql][Tips][EN] Let’s get started COALESCE command for WHERE query if data is null or not

[sql][Tips][EN] Let’s get started COALESCE command for WHERE query if data is null or not

For SQL command, before I check data entries are null or not null, I mayset a lot of query in WHERE command more and more.

Let’s use COALESCE command, so that the command use for checking data entries if the data is null, it use first parameter or if the data is not null, it use instead of second parameter.

COALESCE(‘DATA ENTRY’,SAME FIELD)

— to create variable for test query

DECLARE @DataForTest VARCHAR(20) = NULL

WHERE LotNoX = COALESCE(@DataForTest,LotNoX)

WHERE LotNoX = CASEWHEN @DataForTest IS NULL THEN LotNoXELSE @DataForTest END

***pattern 2 CASE WHEN…THEN…ELSE…END

IF @DataForTest IS NOT NULL

WHERE LotNoX = @DataForTest

Finally, when I use COALECE command, It makes me more short statement and easy to write the query.

SQL, COALESCE, Microsoft SQL Server


Originally published at windowsdna.blogspot.com on June 17, 2015.

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.