USP_SEARCHLIST_MEMBERSHIPPROMO

Find a specific membership promotion

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Name
@PROMOTIONTYPECODE	tinyint	IN	Promotion type
@DISCOUNTCALCULATIONTYPECODE	tinyint	IN	Discount type
@APPLICATIONTYPECODE	tinyint	IN	Application type
@INCLUDEINACTIVE	bit	IN	Include inactive
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



CREATE procedure dbo.USP_SEARCHLIST_MEMBERSHIPPROMO
(
    @NAME nvarchar(100) = null,
    @PROMOTIONTYPECODE tinyint = null,
    @DISCOUNTCALCULATIONTYPECODE tinyint = null,
    @APPLICATIONTYPECODE tinyint = null,
    @INCLUDEINACTIVE bit = null,
    @MAXROWS smallint = 500
)
as
    set @NAME = COALESCE(@NAME,'') + '%' ;

    select top(@MAXROWS)
        ID,
        NAME,
        FORMATTEDVALUE
    from 
        dbo.MEMBERSHIPPROMO
    where
         (NAME LIKE @NAME) and
         (@PROMOTIONTYPECODE is null or PROMOTIONTYPECODE = @PROMOTIONTYPECODE) and
         (@DISCOUNTCALCULATIONTYPECODE is null or DISCOUNTCALCULATIONTYPECODE = @DISCOUNTCALCULATIONTYPECODE) and
         (@APPLICATIONTYPECODE is null or APPLICATIONTYPECODE = @APPLICATIONTYPECODE) and
         (@INCLUDEINACTIVE = 1 or ISACTIVE = 1)
    order by 
        NAME asc