HereToLearn_ HereToLearn_ - 1 year ago 66
MySQL Question

SQL SP: How to return one inactive record along with active records

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;
IF isnull(@specialtyId,'')=''
SELECT SpecialtyID AS Id, Specialty AS Name
FROM dbo.Specialties
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
AND (max(SpecialtyID) IS NOT NULL)

Answer Source

It seems to me that this can be done with no IF whatsoever:

SELECT  s.SpecialtyID AS Id, 
        s.Specialty AS Name
FROM dbo.Specialties s
WHERE specialtyID = @specialtyId
OR IsActive = 1;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download