SQL Server Stored Procedure – Using CASE Statement

sql serversql-server-2016

I am building this where condition by concatenating the @whereClause variable in my stored procedure. At this point I am using the case to add additional values. if there a better way to do this?

SET @whereClause  = 
    ( CASE  
        -- APPType Clinical
         WHEN (@ApplicationType = 'Clinical') THEN @whereClause + ' AND vpgl.AppType = Clinical'
         -- APPType Patients
         WHEN (@ApplicationType = 'Patients') THEN @whereClause + ' AND vpgl.AppType = Patients'
          -- APPType Web
         WHEN (@ApplicationType = 'Web') THEN @whereClause + ' AND vpgl.AppType = null'
         ELSE @whereClause
       END  
    )

Best Answer

I think this is a better, safer, more self-documenting, and more scalable approach:

IF @ApplicationType IN ('Clinical','Patients')
BEGIN
  SET @whereClause += N' AND vpgl.AppType = @ApplicationType';
END

IF @ApplicationType = 'Web'
BEGIN
  SET @whereClause += N' AND vpgl.AppType IS NULL';
END

...
SET @sql = @sql + @whereClause;

EXEC sys.sp_executesql @sql,
     N'@ApplicationType varchar(32)',
     @ApplicationType;

If you really, really want to use a CASE expression (which is not meant for control of flow), you can say:

SET @whereClause += CASE 

   WHEN @ApplicationType IN ('Clinical','Patients')
   THEN N' AND vpgl.AppType = @ApplicationType'

   WHEN @ApplicationType = 'Web'
   THEN N' AND vpgl.AppType IS NULL';

   ELSE N'' END;

...
SET @sql = @sql + @whereClause;

EXEC sys.sp_executesql @sql,
     N'@ApplicationType varchar(32)',
     @ApplicationType;
Related Question