CODEWITHSUNDEEP
sqlsql-servert-sql
Art F
Art F

Reputation: 4202

Modifying a Join SQL Server

I have the following Query in an SQL Server stored procedure

SELECT SZ_SK FROM tbl_Class CC
INNER JOIN tbl_Location LR ON CC.LCTN_RSTN = LR.LCTN_RSTN
WHERE LR.LCTN_RSTN = ISNULL(@Location,LR.LCTN_RSTN)

I have a problem though, I realized that in the tbl_Class the LCTN_RSTN value could be NULL, tbl_Location doesn't have a NULL field. This is an issue since if @Location = NULL then I want fields where CC.LCTN_RSTN= NULL to show up. Any way I can do that? (SZ_SK only exists in tbl_Class.)

Upvotes: 0

Views: 46

Answers (2)

rs.
rs.

Reputation: 27457

Try this

SELECT SZ_SK FROM tbl_Class CC
INNER JOIN tbl_Location LR 
     ON coalesce(CC.LCTN_RSTN,'') = coalesce(LR.LCTN_RSTN,'')
WHERE (@Location is null) or (coalesce(LR.LCTN_RSTN,'') = @Location)

Upvotes: 1

Godeke
Godeke

Reputation: 16281

Assuming LCTN_RSTN is a string and you don't use empty strings anywhere

SELECT SZ_SK FROM tbl_Class CC
INNER JOIN tbl_Location LR ON ISNULL(CC.LCTN_RSTN, '') = ISNULL(LR.LCTN_RSTN, '')
WHERE LR.LCTN_RSTN = ISNULL(@Location,LR.LCTN_RSTN)

Would join the nulls. NULL != NULL, but converting to empty strings, '' = ''

The basic technique here is to use a sentinel value outside your actual range. If LCTN_RSTN is IDENTITY(1,1), for example, use -1 as your default for nulls.

Upvotes: 1

Related Questions