USP_CREATEORUPDATE_BUILDFULLADDRESS

            CREATE procedure dbo.USP_CREATEORUPDATE_BUILDFULLADDRESS 
            with execute as caller
            as
                set nocount on;

                declare @SQL nvarchar(max);
                declare @CREATEORALTER nvarchar(7);

                if exists(select object_id from sys.objects where type = 'FN' and name = 'UFN_BUILDFULLADDRESS')
                    set @CREATEORALTER = 'alter ';
                else
                    set @CREATEORALTER = 'create ';

                set @SQL = @CREATEORALTER + 'function dbo.UFN_BUILDFULLADDRESS(@ADDRESSID uniqueidentifier, @ADDRESSBLOCK nvarchar(150),    @CITY nvarchar(50),    @STATEID uniqueidentifier,    @POSTCODE nvarchar(12),    @COUNTRYID uniqueidentifier) returns nvarchar(300) with execute as caller' + nchar(13);
                set @SQL = @SQL + '/*This code was auto-generated by calling dbo.USP_CREATEORUPDATE_BUILDFULLADDRESS on ' + cast(getdate() as nvarchar) + '.  */'+ nchar(13);
                set @SQL = @SQL + ' as begin' + nchar(13);
                set @SQL = @SQL + 'declare @COUNTRYADDRESSFORMAT uniqueidentifier'+  nchar(13);
                set @SQL = @SQL + '/*if @COUNTRYID is null*/' + nchar(13);
                set @SQL = @SQL + '/*get default country for system?*/' + nchar(13);
                set @SQL = @SQL + '/*based on country figure out how to format the address*/' + nchar(13);
                set @SQL = @SQL + 'set @ADDRESSBLOCK = coalesce(@ADDRESSBLOCK, '''');' + nchar(13);
                set @SQL = @SQL + 'set @CITY = coalesce(@CITY, '''');' + nchar(13);
                set @SQL = @SQL + 'set @POSTCODE = coalesce(@POSTCODE, '''');' + nchar(13);
                set @SQL = @SQL + 'select @COUNTRYADDRESSFORMAT = COUNTRYADDRESSFORMATID from dbo.COUNTRY where ID = @COUNTRYID' + nchar(13);
                set @SQL = @SQL + nchar(13);

                declare @ID uniqueidentifier;
                declare @FORMATNAME nvarchar(40);
                declare @FORMATSQLFUNCTION nvarchar(200);

                declare COUNTRYADDRESSFORMAT_CURSOR cursor local fast_forward for
                    select
                        ID,
                        FORMATNAME,
                        FORMATSQLFUNCTION
                    from
                        dbo.COUNTRYADDRESSFORMAT
                    order by
                        SEQUENCE;

                open COUNTRYADDRESSFORMAT_CURSOR;

                fetch next from COUNTRYADDRESSFORMAT_CURSOR
                into @ID, @FORMATNAME, @FORMATSQLFUNCTION;

                -- Check @@fetch_status to see if there are any more rows to fetch.

                while @@fetch_status = 0 begin

                    set @SQL = @SQL + 'if @COUNTRYADDRESSFORMAT = '''+cast(@ID as nvarchar(36))+ ''''+ nchar(13);
                    set @SQL = @SQL + 'return dbo.'+@FORMATSQLFUNCTION+'(@ADDRESSID, @ADDRESSBLOCK,@CITY,@STATEID,@POSTCODE,@COUNTRYID)'+ nchar(13);
                    set @SQL = @SQL + nchar(13);

                    -- This is executed as long as the previous fetch succeeds.

                    fetch next from COUNTRYADDRESSFORMAT_CURSOR
                    into @ID, @FORMATNAME, @FORMATSQLFUNCTION;

                end

                close COUNTRYADDRESSFORMAT_CURSOR;
                deallocate COUNTRYADDRESSFORMAT_CURSOR;

                set @SQL = @SQL + 'return ''''';
                set @SQL = @SQL + nchar(13);
                set @SQL = @SQL + 'end';

                exec (@SQL);