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; BEGIN IF isnull(@specialtyId,'')='' BEGIN SELECT SpecialtyID AS Id, Specialty AS Name FROM dbo.Specialties WHERE IsActive = 1 ORDER BY Specialty; END ELSE BEGIN 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; END END