USP_SEARCHLIST_DISCOUNT

Search through the discounts in the system

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Name
@DISCOUNTTYPECODE	tinyint	IN	Type
@APPLIESTOCODE	tinyint	IN	Applies to
@CALCULATIONTYPECODE	tinyint	IN	Calculation type
@APPLICATIONTYPECODE	tinyint	IN	Selected for use
@INCLUDEINACTIVE	bit	IN	Include inactive
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.
@INCLUDETICKETDISCOUNTS	bit	IN	Include ticket discounts
@INCLUDEMERCHANDISEDISCOUNTS	bit	IN	Include merchandise discounts

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_DISCOUNT
(
    @NAME nvarchar(100) = null,
    @DISCOUNTTYPECODE tinyint = null,
    @APPLIESTOCODE tinyint = null,
    @CALCULATIONTYPECODE tinyint = null,
    @APPLICATIONTYPECODE tinyint = null,
    @INCLUDEINACTIVE bit = 0,
    @MAXROWS smallint = 500,
    @INCLUDETICKETDISCOUNTS bit = 0,
    @INCLUDEMERCHANDISEDISCOUNTS bit = 0
)
as
    set @NAME = coalesce(@NAME,'') + '%';

    select top(@MAXROWS)
        ID,
        NAME,
        DISCOUNTTYPE,
        APPLIESTO,
        CALCULATIONTYPE,
        APPLICATIONTYPE,
        ISACTIVE
    from dbo.DISCOUNT
    where SUPERSEDEDBYID is null
        and (NAME like @NAME)
        and (@DISCOUNTTYPECODE is null or DISCOUNTTYPECODE = @DISCOUNTTYPECODE)
        and (@APPLIESTOCODE is null or APPLIESTOCODE = @APPLIESTOCODE)
        and (@CALCULATIONTYPECODE is null or CALCULATIONTYPECODE = @CALCULATIONTYPECODE)
        and (@APPLICATIONTYPECODE is null or APPLICATIONTYPECODE = @APPLICATIONTYPECODE)
        and (@INCLUDEINACTIVE = 1 or ISACTIVE = 1)
        and
        (
            (@INCLUDETICKETDISCOUNTS = 1 and APPLIESTOTICKETS = 1)
            or
            (@INCLUDEMERCHANDISEDISCOUNTS = 1 and APPLIESTOMERCHANDISE = 1)
        )
    order by 
        NAME asc