SQL Server stored procedure using case statement and variables "blows up" and won't f-CodePudding

I feed multiple variables to a stored procedure using a case statement. Two of the variables are uniqueidentifier that should cause it to return one row always.

However, I am forced to use a top 1 to get it to avoid an error stating my sub select is wrong or it returns hundreds of thousands of rows and acts as if it is ignoring all of the and statements to force it to filter down to one row. Something is causing the case statement not to treat the WHEN statements together as they should always return either yes or no.

ALTER PROCEDURE [dbo].[test_GetUserID_Date]
    (@Enterpriseid     CHAR(5),
     @PracticeID       CHAR(4),
     @person_id        UNIQUEIDENTIFIER,
     @pi_encounter_id  UNIQUEIDENTIFIER,
     @user_id          INTEGER,
     @encdate          VARCHAR(10),
     @is_valid         CHAR(1) OUTPUT)
AS
BEGIN
    SET @is_valid = (SELECT TOP 1
                         CASE 
                             WHEN pe.enterprise_id = @Enterpriseid  
                                  AND pe.practice_id = @PracticeID 
                                  AND pe.person_id = @person_id 
                                  AND pe.enc_id = @pi_encounter_id
                                  AND pe.created_by  = @user_id  
                                  AND CONVERT(VARCHAR, GETDATE(), 112) = @encdate
                                 THEN  'Y' 
                                 ELSE 'N'
                         END 
                     FROM patient_encounter pe)
END

CodePudding user response：

The purpose of a CASE expression is to manipulate the value of a particular field within each and every row returned by a query.

If you run this query, what value do you get back?

SELECT COUNT(1)
FROM Patient_Encounter PE
;

Hundreds of thousands, like you said? - at least, that's what I assume. Because your CASE expression doesn't say "Only return the row where all of these parameters match the field values". What your CASE expression (in fact, any CASE expression) actually says is : "For every row returned from Patient_Encounter, if all of these parameters match, give me a 'Y'. Otherwise, give me an 'N'."

If you want to return only the row where those parameters match, the correct way would be to use those parameter checks within the WHERE clause of the query. Your query doesn't even have a WHERE clause, which means you'll be getting every single row in Patient_Encounter.

Try this:

ALTER PROCEDURE [dbo].[test_GetUserID_Date](
     @Enterpriseid     CHAR(5)
    ,@PracticeID       CHAR(4)
    ,@person_id        UNIQUEIDENTIFIER
    ,@pi_encounter_id  UNIQUEIDENTIFIER
    ,@user_id          INTEGER
    ,@encdate          VARCHAR(10)
    ,@is_valid         CHAR(1) OUTPUT
)
AS
BEGIN
    IF EXISTS (
        SELECT TOP 1 1
        FROM Patient_Encounter PE
        WHERE PE.enterprise_id = @Enterpriseid  
        AND PE.practice_id = @PracticeID 
        AND PE.person_id = @person_id 
        AND PE.enc_id = @pi_encounter_id
        AND PE.created_by  = @user_id  
        AND CONVERT(VARCHAR, GETDATE(), 112) = @encdate
    )
        SET @is_valid = 'Y'
    ELSE
        SET @is_valid = 'N'
END

You wouldn't have to use TOP 1 here, but I do, because I assumed from what you wrote that the presence of 1 row, or 10, or 1,000 means we should get a 'Y'.

You can test this outside of the procedure by just running it as a regular query:

DECLARE
     @Enterpriseid     CHAR(5)
    ,@PracticeID       CHAR(4)
    ,@person_id        UNIQUEIDENTIFIER
    ,@pi_encounter_id  UNIQUEIDENTIFIER
    ,@user_id          INTEGER
    ,@encdate          VARCHAR(10)
    ,@is_valid         CHAR(1)
;
IF EXISTS (
    SELECT TOP 1 1
    FROM Patient_Encounter PE
    WHERE PE.enterprise_id = @Enterpriseid  
    AND PE.practice_id = @PracticeID 
    AND PE.person_id = @person_id 
    AND PE.enc_id = @pi_encounter_id
    AND PE.created_by  = @user_id  
    AND CONVERT(VARCHAR, GETDATE(), 112) = @encdate
)
    SET @is_valid = 'Y'
ELSE
    SET @is_valid = 'N'
;
SELECT @is_valid
;

CodePudding user response：

Thanks 3BK this answer worked.

You wouldn't have to use TOP 1 here, but I do, because I assumed from what you wrote that the presence of 1 row, or 10, or 1,000 means we should get a 'Y'.

You can test this outside of the procedure by just running it as a regular query:

DECLARE @Enterpriseid CHAR(5) ,@PracticeID CHAR(4) ,@person_id UNIQUEIDENTIFIER ,@pi_encounter_id UNIQUEIDENTIFIER ,@user_id INTEGER ,@encdate VARCHAR(10) ,@is_valid CHAR(1) ; IF EXISTS ( SELECT TOP 1 1 FROM Patient_Encounter PE WHERE PE.enterprise_id = @Enterpriseid
AND PE.practice_id = @PracticeID AND PE.person_id = @person_id AND PE.enc_id = @pi_encounter_id AND PE.created_by = @user_id
AND CONVERT(VARCHAR, GETDATE(), 112) = @encdate ) SET @is_valid = 'Y' ELSE SET @is_valid = 'N' ; SELECT @is_valid ;