SQL Server 2016 - Get NULL values from a table using a Stored Procedure

Question

I am trying to use a stored procedure called GetCompanies and a parameter called @ShowCompaniesWithoutClients in order to show all companies (from a table called Company) where the Client column is NULL or empty ('').

At the moment the solution I found is make an IF/ELSE statement where validate IF @ShowCompaniesWithoutClients=1 I order to

SELECT *  
FROM [Company] 
WHERE [Client] IS NULL OR [Client] = ''

Otherwise, select all columns (without restrictions).

Can anyone help me refactoring this solution replacing it with a solution where I don't need to SELECT statement twice?

Disclaimer: this is just an example of real application, which I have around 20 columns and many more parameters.

eshirvana · Accepted Answer · 2021-03-11 15:33:51Z

2

so @ShowCompaniesWithoutClients as bit can have 3 possible values , 0 , 1 and null. 0 and 1 is clear but when you pass @ShowCompaniesWithoutClients = null means everything should be returned, here is how you can do it :

SELECT * 
FROM [Company] 
WHERE  (ISNULL([Client],'') = ''  and @ShowCompaniesWithoutClients  = 1)
    OR (ISNULL([Client],'') <> '' and @ShowCompaniesWithoutClients  = 0)
    OR (@ShowCompaniesWithoutClients IS NULL)

answered Mar 11, 2021 at 15:33

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Intenzion Over a year ago

Thanks a lot, this it just what I needed!

Collectives™ on Stack Overflow

SQL Server 2016 - Get NULL values from a table using a Stored Procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related