USP_SEARCHLIST_SCHEDULEDPROGRAMACTIVE

Simple way to select an active scheduled program.

Parameters

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

Definition

Copy



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

                select distinct top(@MAXROWS)
                    PROGRAM.ID,
                    NAME,
                    DESCRIPTION,
                    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 = 0)
                order by NAME asc