Hello there - I'm a grandad using SQL and this is my first post - please be gentle with me. I used to work for IBM but that's many years ago now.......
Thanks, in advance, for any help you may be able to offer.
Given, as an example, the following query extracted from a TVF against the T_ServiceProviders table. The ProviderName is NVARCHAR, unique and the primary key. The other columns are all BIT and NOT NULL.. Input parameters (BIT) to the TVF correspond to each service provider type to be returned: manufacturer, supplier, etc and are set depending on whether the user is seeking only manufacturers, only suppliers, both manufacturers and suppliers, etc - I'm sure you get the picture.
My problem is how to construct the Where clause. If I simply use the input parameters in the Where clause I'll get the wrong result set as, assuming a request for manufacturers only, I'd also be retrieving records where IsSupplier, IsWarrantyGuaranteeProvider and IsMaintenanceProvider is zero. Currently, I'm using a lot of If statements to construct the Where clause but, having added a new service provider type, the number of If statements, by my reckoning, will amount to factorial 4. There must be a better way, methinks.
Please tell me I've overlooked something obvious and I've not wasted too much of anyone's time.
SELECT ServiceProvider_ID, ProviderName, IsMaintenanceProvider, IsManufacturer, IsSupplier, IsWarrantyGuaranteeProvider FROM T_ServiceProviders WHERE ( (IsMaintenanceProvider = 0) OR (IsManufacturer = 1) OR (IsSupplier = 0) OR (IsWarrantyGuaranteeProvider = 0) );