USP_SEARCHLIST_STEWARDSHIPPLANTEMPLATE

Search for a stewardship plan template.

Parameters

Parameter	Parameter Type	Mode	Description
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@NAME	nvarchar(100)	IN	Name
@SITEID	uniqueidentifier	IN	Site
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_STEWARDSHIPPLANTEMPLATE
(
    @CURRENTAPPUSERID uniqueidentifier = null,
    @NAME nvarchar(100) = null,
    @SITEID uniqueidentifier = null,
    @MAXROWS smallint = 500
)
as begin
    set nocount on;

    select top(@MAXROWS)
        STEWARDSHIPPLANTEMPLATE.ID,
        STEWARDSHIPPLANTEMPLATE.NAME,
        dbo.UFN_STEWARDSHIPPLANTEMPLATE_GETSITELIST(STEWARDSHIPPLANTEMPLATE.ID) SITELIST
    from
        dbo.STEWARDSHIPPLANTEMPLATE
    where (@NAME is null or STEWARDSHIPPLANTEMPLATE.NAME like @NAME + '%') and
        ((@SITEID IS NULL and not exists (select 1 from dbo.STEWARDSHIPPLANTEMPLATESITE 
                where STEWARDSHIPPLANTEMPLATESITE.STEWARDSHIPPLANTEMPLATEID = STEWARDSHIPPLANTEMPLATE.ID)) or 
            (exists (select 1 from dbo.STEWARDSHIPPLANTEMPLATESITE 
                where STEWARDSHIPPLANTEMPLATESITE.STEWARDSHIPPLANTEMPLATEID = STEWARDSHIPPLANTEMPLATE.ID and
                dbo.UFN_SITEALLOWEDFORUSER(@CURRENTAPPUSERID,STEWARDSHIPPLANTEMPLATESITE.SITEID)=1))) and 
        (@SITEID IS NULL or 
            STEWARDSHIPPLANTEMPLATE.ID in (select STEWARDSHIPPLANTEMPLATESITE.STEWARDSHIPPLANTEMPLATEID
                from dbo.STEWARDSHIPPLANTEMPLATESITE
                where STEWARDSHIPPLANTEMPLATESITE.SITEID = @SITEID))    
    order by NAME
end