/**
*    Description:
*    Gets the country code for the USA.  Used if the default country in SysFile is not specified.
*/

CREATE PROCEDURE [dbo].[GetUsaCountryCodeStandardId]
as
begin
    set nocount on

    ;with UsaCountries as
    (
        select
            CountryCodeStandardId,
            SortOrder = 1
        from CountryCodeStandard
        where VanlineCountryCode = 'USA'
        union
        select
            CountryCodeStandardId,
            SortOrder = 1
        from CountryCodeStandard
        where Alpha2Code = 'US'
        union
        select
            CountryCodeStandardId,
            SortOrder = 1
        from CountryCodeStandard
        where CountryName = 'United States Of America'
        union
        select
            CountryCodeStandardId,
            SortOrder = 1
        from CountryCodeStandard
        where CountryName = 'United States'
        union
        select
            CountryCodeStandardId,
            SortOrder = 2
        from CountryCodeStandard
        where CountryName = 'Unknown'
    )
    select top 1
        CountryCodeStandardId
    from UsaCountries
    order by SortOrder
end
GO
GRANT EXECUTE ON  [dbo].[GetUsaCountryCodeStandardId] TO [MssExec]
GO