1

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
  • Could you possibly include sample data and desired result? Because, it looks to me like a simple WHERE IsActive = 1 OR specialtyId = @specialtyId should work – Forrest Sep 27 '16 at 13:51
  • Is this for MySQL or for SQL Server? You have tagged both. – ypercubeᵀᴹ Sep 27 '16 at 13:56
  • wow @Forrest thanks for that. That is exactly what I was looking for and can't believe I missed it. – HereToLearn_ Sep 27 '16 at 13:59
  • ( SELECT one inactive row ) UNION ALL ( SELECT all active rows ) – Rick James Sep 28 '16 at 3:45
  • Note: cross-posted to StackOverflow – RDFozz Oct 4 '18 at 19:09
1

It looks to me like a simple WHERE IsActive = 1 OR specialtyId = @specialtyId should work.

1

For anyone who sees this in the future, the StackOverflow copy of this same question has a fuller answer.

For easy viewing, I copied Lamak's code from the link to below:

SELECT  s.SpecialtyID AS Id, 
        s.Specialty AS Name
FROM dbo.Specialties s
WHERE specialtyID = @specialtyId
OR IsActive = 1;
  • @RDFozz Updated wording. You are correct, the answer isn't really explained any better but it is a fuller more complete answer. Also, the comments in the post on SO were helpful to me. – AFatBunny Oct 4 '18 at 19:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.