1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Intenzion
  • 81
  • 1
  • 7

1 Answers1

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)
eshirvana
  • 23,227
  • 3
  • 22
  • 38