USP_SEARCHLIST_EDUCATIONALINSTITUTION

This provides the ability to search for an educational institution.

Parameters

Parameter	Parameter Type	Mode	Description
@NAME	nvarchar(100)	IN	Educational institution
@FICECODE	nvarchar(50)	IN	FICE code
@CITY	nvarchar(150)	IN	City
@STATEID	uniqueidentifier	IN	State
@COUNTRYID	uniqueidentifier	IN	Country
@INCLUDEAFFILIATED	bit	IN	Include affiliated institutions
@MAXROWS	smallint	IN	Input parameter indicating the maximum number of rows to return.

Definition

Copy



            CREATE procedure dbo.USP_SEARCHLIST_EDUCATIONALINSTITUTION
            (
                @NAME nvarchar(100) = null,
                @FICECODE nvarchar(50) = null,
                @CITY nvarchar(150) = null,
                @STATEID uniqueidentifier = null,
                @COUNTRYID uniqueidentifier = null,
                @INCLUDEAFFILIATED bit = null,
                @MAXROWS smallint = 500
            )
            as
                set nocount on;

                set @NAME = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@NAME, 0, null);
                set @FICECODE = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@FICECODE, 0, null);
                set @CITY = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@CITY, 0, null);

                select top (@MAXROWS)
                    INSTITUTION.ID,
                    INSTITUTION.NAME,
                    INSTITUTION.CITY,
                    STATE.DESCRIPTION as STATE,
                    COUNTRY.DESCRIPTION as COUNTRY
                from dbo.EDUCATIONALINSTITUTION as INSTITUTION
                    left join dbo.STATE on INSTITUTION.STATEID = STATE.ID
                    left join dbo.COUNTRY on INSTITUTION.COUNTRYID = COUNTRY.ID    
                where (@NAME is null or INSTITUTION.NAME like @NAME + '%')
                    and (@FICECODE is null or INSTITUTION.FICECODE like @FICECODE + '%')
                    and (@CITY is null or INSTITUTION.CITY like @CITY + '%')
                    and (@STATEID is null or @STATEID = INSTITUTION.STATEID)
                    and (@COUNTRYID is null or @COUNTRYID = INSTITUTION.COUNTRYID)
                    and (@INCLUDEAFFILIATED is null 
                        or @INCLUDEAFFILIATED = 1
                        or (@INCLUDEAFFILIATED = 0 and INSTITUTION.ISAFFILIATED = 0))