I am working on this peculiar sql stored proc where the business case is as follows:
Business Case: Table Specialties contains all the Specialties and there is a bit field for each record telling if it's Active or Inactive. We always display only the active records from that table in form of dropdown. Users may select a Specialty which can later on be deactivated. New requirement is to be able to pull that Inactive record along with all the active records in the result set.
Here's how I thought I should do this: If no specialty is assigned to the person I am pulling up then the dropdown is going to be populated by all active records. If there is a inactive specialty associated with the person I am pulling up then I send that specialtyID in stored proc as a parameter and return that inactive records along with active records to populate the dropdown.
Below is what I got so far: So far if I dont pass in any specialtyId then I am returning active specialty records which is working. When I send in a specialtyId parameter then it just returns that one inactive record but not rest of the other active records. I need the rest of the active records too along with that one inactive record.
DECLARE @specialtyId INT = null;
SELECT SpecialtyID AS Id, Specialty AS Name
WHERE IsActive = 1
ORDER BY Specialty;
SET @specialtyId = @specialtyId ;
SELECT s.SpecialtyID AS Id, s.Specialty AS Name
FROM dbo.Specialties s
WHERE specialtyId = @specialtyId
GROUP BY s.Specialty, s.SpecialtyID
HAVING (Specialty IS NOT NULL)
AND (max(SpecialtyID) IS NOT NULL)
ORDER BY Name;