USP_SEARCHLIST_PROGRAM

Simple way to select a program

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(50)	IN	Name
@EVENTLOCATION	uniqueidentifier	IN	Location
@PROGRAMCATEGORYCODEID	uniqueidentifier	IN	Category
@INCLUDEINACTIVE	bit	IN	Include inactive
@INCLUDEDAILYADMISSION	bit	IN	Include daily admission
@SALESMETHODTYPECODE	tinyint	IN	Sales method
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@INCLUDEPREREGISTEREDPROGRAMS	bit	IN	Include preregistered programs

Definition

Copy



              CREATE procedure dbo.USP_SEARCHLIST_PROGRAM
              (
                @NAME nvarchar(50) = null,
                @EVENTLOCATION uniqueidentifier = null,
                @PROGRAMCATEGORYCODEID uniqueidentifier = null,
                @INCLUDEINACTIVE bit = null,
                @INCLUDEDAILYADMISSION bit = null,
                @SALESMETHODTYPECODE tinyint = null,
                @MAXROWS smallint = 500,
                @INCLUDEPREREGISTEREDPROGRAMS bit = 1
              )
              as
                 set @NAME = REPLACE(@NAME,'*','%');
                 set @NAME = COALESCE(@NAME,'') + '%' ;

                select distinct top(@MAXROWS)
                    PROGRAM.ID,
                    PROGRAM.NAME,
                    PROGRAM.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 = 1 or @INCLUDEINACTIVE = 1) and
                    (ISDAILYADMISSION = 0 or @INCLUDEDAILYADMISSION = 1) and
                    (ISPREREGISTERED = 0 or @INCLUDEPREREGISTEREDPROGRAMS = 1)
                    and
                    (
                        @SALESMETHODTYPECODE is null or
                        exists
                        (
                            select 1 from dbo.PROGRAMSALESMETHOD with (nolock)
                            inner join dbo.SALESMETHOD with (nolock) on
                                PROGRAMSALESMETHOD.SALESMETHODID = SALESMETHOD.ID
                            where
                                PROGRAMSALESMETHOD.PROGRAMID = PROGRAM.ID and
                                SALESMETHOD.TYPECODE = @SALESMETHODTYPECODE
                        )
                    )
                order by NAME asc