USP_SEARCHLIST_PROGRAMACTIVE

Simple way to select an active program

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(50)	IN	Name
@EVENTLOCATION	uniqueidentifier	IN	Location
@PROGRAMCATEGORYCODEID	uniqueidentifier	IN	Category
@ISDAILYADMISSION	bit	IN	Include daily admission
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



              create procedure dbo.USP_SEARCHLIST_PROGRAMACTIVE
              (
                @NAME nvarchar(50) = null,
                @EVENTLOCATION uniqueidentifier = null,
                @PROGRAMCATEGORYCODEID uniqueidentifier = null,
                @ISDAILYADMISSION bit = null,
                @MAXROWS smallint = 500
              )
              as
                 set @NAME = REPLACE(@NAME,'*','%');
                 set @NAME = COALESCE(@NAME,'') + '%' ;

                select distinct top(@MAXROWS)
                    PROGRAM.ID,
                    NAME,
                    DESCRIPTION,
                    case when ISDAILYADMISSION = 1 then 'Daily admission' else 'Scheduled program' end,
                    dbo.UFN_PROGRAMCATEGORYCODE_GETDESCRIPTION(PROGRAM.PROGRAMCATEGORYCODEID) as PROGRAMCATEGORYCODEID
                from dbo.PROGRAM
                left join dbo.PROGRAMLOCATION on PROGRAMLOCATION.PROGRAMID = PROGRAM.ID
                where (@NAME is null or NAME LIKE @NAME) and 
                    (@EVENTLOCATION is null or PROGRAMLOCATION.EVENTLOCATIONID = @EVENTLOCATION) and
                    (@PROGRAMCATEGORYCODEID is null or PROGRAM.PROGRAMCATEGORYCODEID = @PROGRAMCATEGORYCODEID) and
                    (ISACTIVE <> 0) and
                    (ISDAILYADMISSION = @ISDAILYADMISSION or ISDAILYADMISSION = 0)
                order by NAME asc