USP_VENDOR1099_NAMEANDADDRESSSPREVIEW_VIEW

                create procedure dbo.USP_VENDOR1099_NAMEANDADDRESSSPREVIEW_VIEW(
          --The @ID field is a concatenation of 4 GUIDs separated by semicolons. The first is an ID 

          -- for the Alias table and it can be empty. The second is also an ID for the Alias table 

          -- and it can be empty. The third is an ID for the Address table and it can be empty. 

          -- The forth is the vendor ID and it is required.

                    @ID nvarchar(148)
                    ,@DATALOADED bit = 0 output
                    ,@NAMELINE1 nvarchar(40) = null output
                    ,@NAMELINE2 nvarchar(40) = null output
                    ,@ADDRESSLINE1 nvarchar(40) = null output
                    ,@ADDRESSLINE2 nvarchar(40) = null output
                    ,@CITYSTATEZIP nvarchar(40) = null output
                )
                as
                begin
          declare @NAMELINE1ID nvarchar(36);
          declare @NAMELINE2ID nvarchar(36);
          declare @ADDRESSID nvarchar(36);
          declare @INDEX1 integer;
          declare @VENDORID nvarchar(36);
                    declare @NAME1 nvarchar(300);
                    declare @NAME2 nvarchar(300);
                    declare @ADDRESSBLOCK nvarchar(150);
                    declare @CITY nvarchar(50);
                    declare @STATE nvarchar(2);
                    declare @POSTCODE nvarchar(12);
                    declare @LINES as table (LINE  nvarchar(150), ID integer);
                    declare @INDEX as integer;
                    declare @STARTINDEX as integer;
                    declare @ADDRESSBLOCKLEN as integer;
                    declare @COUNT as integer;

                    set @DATALOADED = 0;

          set @INDEX = CHARINDEX(';', @ID);
          set @INDEX1 = CHARINDEX(';', @ID, @INDEX + 1);

          set @NAMELINE1ID = SUBSTRING(@ID, 0, @INDEX);
          set @NAMELINE2ID = SUBSTRING(@ID, @INDEX + 1, @INDEX1 - (@INDEX + 1));
          set @INDEX = CHARINDEX(';', @ID, @INDEX1 + 1);
          set @ADDRESSID = SUBSTRING(@ID, @INDEX1 + 1, @INDEX - (@INDEX1 + 1));
          set @VENDORID = SUBSTRING(@ID, @INDEX + 1, LEN(@ID) - @INDEX);

          if LEN(@NAMELINE1ID) > 0
            select @DATALOADED = 1,
                   @NAME1 = coalesce(A1.NAME, '')
            from dbo.ALIAS A1
            where A1.ID = @NAMELINE1ID;
          else
            select @DATALOADED = 1,
                   @NAME1 = coalesce(C.NAME, '')
            from dbo.CONSTITUENT C
            where C.ID = @VENDORID;

          if LEN(@NAMELINE2ID) > 0
            select @DATALOADED = 1,
                   @NAME2 = coalesce(A2.NAME, '')
            from dbo.ALIAS A2
            where A2.ID = @NAMELINE2ID;
          else
            set @NAME2 = '';

          if LEN(@ADDRESSID) > 0
            select @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
                               @CITY = ADDRESS.CITY,
                               @STATE = STATE.ABBREVIATION,
                               @POSTCODE = ADDRESS.POSTCODE
            from dbo.ADDRESS
            left outer join dbo.STATE on ADDRESS.STATEID = STATE.ID
            where ADDRESS.ID = @ADDRESSID;
          else
            select @ADDRESSBLOCK = ADDRESS.ADDRESSBLOCK,
                               @CITY = ADDRESS.CITY,
                               @STATE = STATE.ABBREVIATION,
                               @POSTCODE = ADDRESS.POSTCODE
            from dbo.ADDRESS
            left outer join dbo.STATE on ADDRESS.STATEID = STATE.ID
            where ADDRESS.CONSTITUENTID = @VENDORID and ADDRESS.ISPRIMARY = 1;

                    set @NAMELINE1 = '';
                    set @NAMELINE2 = '';
                    set @ADDRESSLINE1 = '';
                    set @ADDRESSLINE2 = '';
                    set @CITYSTATEZIP = '';
                    set @INDEX=0;
                    set @STARTINDEX=0;
                    set @COUNT=0;
                    set @ADDRESSBLOCK = replace(@ADDRESSBLOCK,nchar(13),'');
                    set @ADDRESSBLOCKLEN=len(@ADDRESSBLOCK);

                    -- Begin handle Name Line 1 & 2 --

                    if len(@NAME1) > 40 and len(@NAME2) = 0
                        begin
                            exec dbo.USP_VENDOR1099_LINESPLITTER @NAME1,@NAME1 output,@NAME2 output;
                            set @NAMELINE1 = replace(@NAME1, nchar(10), '');
                            set @NAMELINE2 = replace(@NAME2, nchar(10), '');
                        end
                    else
                        begin
                            set @NAMELINE1 = @NAME1
                            set @NAMELINE2 = @NAME2
                        end

                    -- End handle Name Line 1 & 2 --





                    -- Begin handle Address Line 1 & 2 --


                    while @INDEX<@ADDRESSBLOCKLEN
                        begin
                            set @INDEX = charindex(nchar(10), @ADDRESSBLOCK, @STARTINDEX);
                            if @INDEX=0
                                set @INDEX = @ADDRESSBLOCKLEN+1;            

                            set @INDEX = @INDEX + 1
                            insert into @LINES (LINE,ID) values ( substring(@ADDRESSBLOCK, @STARTINDEX, @INDEX-@STARTINDEX-1),@COUNT );

                            set @STARTINDEX = @INDEX;
                            set @COUNT=@COUNT+1;
                        end

                    if @COUNT>1 
                        begin
                            select @ADDRESSLINE1=left(LINE,40) from @LINES where ID=0;
                            select @ADDRESSLINE2=left(LINE,40) from @LINES where ID=1;
                        end
                    else
                        begin
                            exec dbo.USP_VENDOR1099_LINESPLITTER @ADDRESSBLOCK,@ADDRESSLINE1 output,@ADDRESSLINE2 output;
                            set @ADDRESSLINE1 = replace(@ADDRESSLINE1, nchar(10), '');
                            set @ADDRESSLINE2 = replace(@ADDRESSLINE2, nchar(10), '');
                        end

                    -- End handle Address Line 1 & 2 --





                    -- Begin handle City, State Zip --


                    -- 37, plus 2 spaces and a comma which = 40

                    if len(@CITY) + len(@STATE) + len(@POSTCODE) > 37 begin
                        -- limit POSTCODE to 11 (#####-####)

                        set @POSTCODE = substring(@POSTCODE,0,11)

                        -- limit STATE to 2 ZZ

                        set @STATE = substring(@STATE,0,3)

                        -- limit CITY to remainder.

                        declare @Remainder as int
                        set @Remainder = 36 - len(@STATE) - len(@POSTCODE)
                        print @Remainder
                        set @CITY = substring(@CITY,0,@Remainder)
                    end

                    -- Format City, ST Zip

                    if len(@CITY) > 0
                        set @CITYSTATEZIP = @CITYSTATEZIP + @CITY;
                    if len(@CITY) > 0 and (len(@STATE) > 0 or len(@POSTCODE) > 0)
                        set @CITYSTATEZIP = @CITYSTATEZIP + nchar(44) + nchar(32);
                    if len(@STATE) > 0
                        set @CITYSTATEZIP = @CITYSTATEZIP + @STATE;
                    if (len(@CITY) > 0 or len(@STATE) > 0) and len(@POSTCODE) > 0
                        set @CITYSTATEZIP = @CITYSTATEZIP + nchar(32);
                    if len(@POSTCODE) > 0
                        set @CITYSTATEZIP = @CITYSTATEZIP + @POSTCODE;  

                    -- End handle City, State Zip --




                    return 0;
                end