Use WHERE AND CASE IN when crafting T-SQL-CodePudding

I have a stored procedure that has a few variables that may or may not be passed. they are a list of PKs from other tables, so FKs but formatted in as a string of CSVs.

here's what the query essentially looks like

DECLARE @SomeIds VARCHAR(MAX) = ''
CREATE TABLE #TempIds (Id INT)

IF (@SomeIds = '' OR @SomeIds = NULL) INSERT INTO #TempIds VALUES (NULL)
ELSE INSERT INTO #TempIds SELECT * FROM SplitString(@SomeIds,',') -- SplitString() is a user function

SELECT cont.varchar_LastName AS LastName
      ,cred.varchar_CredentialName AS CredentialName
  FROM [dbo].[tbl_Contacts] AS cont
  LEFT JOIN [dbo].[tbl_ContactsCredentials] AS cc ON cont.pk_int_Id = cc.fk_ContactId
  LEFT JOIN [dbo].[tbl_Credentials] AS cred ON cc.fk_CredentialId = cred.pk_int_Id

So this query basically gives me a full list of contacts both with and without a credential name. I don't have a WHERE clause, so not surprised.

I get data basically like:

LastName  |  CredentialName
---------------------------
Stevens   |  Admin
Arnolds   |  User
Bishop    |  NULL
Evans     |  NULL

So if I add a WHERE clause like this:

WHERE cred.pk_int_Id IN (SELECT * FROM #TempIds)

I get zero results.

When I run this:

SELECT * FROM #TempIds

I get this:

Id
-----------
NULL

When I run it with "real values" in @SomeIds like '1,2' then it works fine.

I presume this is because my WHERE clause is looking in the cred table and there are no NULL values in that table, so that's why I'm not getting anything.

But I'm not sure how I fix it?

I guess I really want to do something like this:

WHERE CredentialName IN (SELECT * FROM #TempIds)

But I believe to do that, I'd have to run the first query into another temp table, then run a second query on that table.

Any help is greatly appreciated.

CodePudding user response：

You can avoid a temp table

WHERE (NULLIF(@SomeIds,'') IS NULL OR cred.pk_int_Id IN (SELECT value FROM SplitString(@SomeIds,',')))

Or if your Sql Server version supports STRING_SPLIT

WHERE (NULLIF(@SomeIds,'') IS NULL OR cred.pk_int_Id IN (SELECT value FROM STRING_SPLIT(@SomeIds,',')))

And then don't initialize @SomeIds to make it get all records.

CodePudding user response：

I would consider using a UNION for this. Putting an OR in a WHERE clause can make for some bad execution plans, and often makes indexes unusable.

SELECT cont.varchar_LastName AS LastName
    ,cred.varchar_CredentialName AS CredentialName
FROM [dbo].[tbl_Contacts] AS cont
LEFT JOIN [dbo].[tbl_ContactsCredentials] AS cc
    ON cont.pk_int_Id = cc.fk_ContactId
LEFT JOIN [dbo].[tbl_Credentials] AS cred
    ON cc.fk_CredentialId = cred.pk_int_Id
WHERE cred.pk_int_Id IN (SELECT value FROM STRING_SPLIT(@SomeIds,','))

UNION ALL

SELECT cont.varchar_LastName AS LastName
    ,cred.varchar_CredentialName AS CredentialName
FROM [dbo].[tbl_Contacts] AS cont
LEFT JOIN [dbo].[tbl_ContactsCredentials] AS cc
    ON cont.pk_int_Id = cc.fk_ContactId
LEFT JOIN [dbo].[tbl_Credentials] AS cred
    ON cc.fk_CredentialId = cred.pk_int_Id
WHERE NULLIF(@SomeIds,'') IS NULL

I talk about using OR in UPDATES statements here. But the same logic applies to SELECT.