/**
*    Gets an XmlSystemOption value as a non-null bit value (0 or 1).  See the GetBooleanVarChar function to see the
*        possible values the XmlSystemOption can be (Y, Yes, 1, true, etc. )
*    @param: @inName - The name of the option. (i.e. SirvaSendLocation)
*    @param: @inExternalSystem - (Optional) The external system (i.e. 'SIRVA Registration Upload'). If this is not specified, the @inXmlSystemDatatypeMap param must be used.
*    @param: @inXmlSystemDatatypeMap - (Optional) Name of the Xml System Data Type Map to look up the Xml System from. This is the preferred method of looking up an Xml System.
*            If this is not specified, the @inExternalSystem param must be used.
*
*    @param  @outOptionBitValue returns a non-null bit value (0 or 1) for the XmlSystemOption.[Value].
*/

create procedure [dbo].[GetXmlSystemOptionBitValue] (
    @inName varchar(128),
    @inExternalSystem varchar(50) = null,
    @inXmlSystemDatatypeMap varchar(64) = null,
    @outOptionBitValue bit output
)
as
begin
    set nocount on

    declare @theXmlSystemValues table
    (
        [Value] varchar(4096)
    )
    insert into @theXmlSystemValues( [Value] )
    exec GetXmlSystemOption
        @inName = @inName,
        @inExternalSystem = @inExternalSystem,
        @inXmlSystemDatatypeMap = @inXmlSystemDatatypeMap

    set @outOptionBitValue = convert( bit, case isnull( ( select dbo.GetBooleanVarChar([Value]) from @theXmlSystemValues where len( [Value] ) <= 128 ), 'false' )
        when 'true' then 1
        else 0
    end )
end
GO
GRANT EXECUTE ON  [dbo].[GetXmlSystemOptionBitValue] TO [MssExec]
GO