SQL procedure, dynamic SQL & a partridge in a petri

Quick overview of this project I am currently working: an external facing ASP site that is linking to a web service over SSL and all data is encrypted coming from SQL Server 2008.

I need input on the SQL code I am being asked to come up with. Basically, they want one record returned per employee which could contain multiple addresses, phone numbers, etc. All record types are in their own distinctive tables, but all in the same db. Here is what I have, is there better way?

Code:

---

--IN STORED PROCEDURE USE THE SEARCH TO GET THE EMPLOYEE EMP_ID AND USE THAT TO FILL TMP TABLE
DECLARE @ID INT

CREATE TABLE #TMP_SELECT(ID BIGINT,
                                                FIRSTNAME  NVARCHAR(25),
                                                MIDDLENAME  NVARCHAR(25),
                                                LASTNAME    NVARCHAR(25),
                                                SUFFIXNAME  NVARCHAR(5),
                                                MAIDENNAME  NVARCHAR(25),
                                                ALIASNAME  NVARCHAR(25),
                                                SSNUM            NVARCHAR(9),
                                                BIRTHDATE  DATETIME,
                                                STARTDATE  DATETIME,
                                                TERMDATE    DATETIME,
                                                EMPGUID                UNIQUEIDENTIFIER
                                                )
                                               
INSERT INTO #TMP_SELECT
SELECT EM.EMP_ID, EM.EMP_First, EM.EMP_Middle, EM.EMP_Last, EM.EMP_Suffix, EM.EMP_Maiden,
        EM.EMP_Alias, EM.EMP_SSN, EM.EMP_DOB, EM.EMP_StartDate, EM.EMP_TermDate, EM.EMP_GUID
FROM EMP01_MASTER AS EM
WHERE EMP_ID = @ID

--ALTER TABLE AND ADD COLUMNS FOR EACH ROW IN THE ADDRESS, PHONE AND EMERGENCY CONTACT TABLE
DECLARE @COLCOUNT INT, @colname as nvarchar(10), @colstring nvarchar(255),
                @SQL NVARCHAR(MAX)

SELECT @colcount = COUNT(*)
FROM GEN01_ADDRESS
--WHERE GEN_FK_ID = @GUID

SELECT @colstring        = '', @SQL = ''

--FOR TESTING PURPOSES
IF @COLCOUNT is null or @COLCOUNT = 0 SELECT @COLCOUNT = 3


--ADDRESSES FIRST
IF @COLCOUNT is not null and @COLCOUNT > 0
BEGIN
        WHILE @COLCOUNT > 0
        BEGIN
                --Get the address type from ontarget
                SELECT @colname = (SELECT top 1(OPT_STRING_VALUE)
                FROM OTForms.dbo.SY98_FORM_OPTIONS
                WHERE OPT_DATABASE_NAME = 'ontarget'
                  AND OPT_TABLE_NAME = 'gen01_address'
                  AND OPT_COL_NAME = 'GEN_ADDTYPE_ID'
                  AND OPT_ENABLED = 1
                  AND CHARINDEX(OPT_STRING_VALUE, @colstring) = 0
                  )
               
                --generate the column name
                SELECT @SQL = @SQL + 'ALTER TABLE #TMP_SELECT ADD ' + @colname + '_ADDRESS nvarchar(450); '
               
                --set counters and string               
                SELECT @COLCOUNT = @COLCOUNT - 1
                SELECT @colstring = CASE WHEN LEN(@colstring) > 0 THEN @colstring + '; ' + @colname ELSE @colname END
        END
END

--reset values
SELECT @colname = '', @colstring = ''

--PHONE(S)
SELECT @colcount = COUNT(*)
FROM GEN02_PHONE
--WHERE GEP_FK_ID = @GUID

--FOR TESTING PURPOSES
IF @COLCOUNT is null or @COLCOUNT = 0 SELECT @COLCOUNT = 3

IF @COLCOUNT is not null and @COLCOUNT > 0
BEGIN
        WHILE @COLCOUNT > 0
        BEGIN
                --Get the address type from ontarget
                SELECT @colname = (SELECT top 1(OPT_STRING_VALUE)
                FROM OTForms.dbo.SY98_FORM_OPTIONS
                WHERE OPT_DATABASE_NAME = 'ontarget'
                  AND OPT_TABLE_NAME = 'GEN02_PHONE'
                  AND OPT_COL_NAME = 'GEN_PHONE_TYPE'
                  AND OPT_ENABLED = 1
                  AND CHARINDEX(OPT_STRING_VALUE, @colstring) = 0
                  )
               
                --generate the column name
                SELECT @SQL = @SQL + 'ALTER TABLE #TMP_SELECT ADD ' + @colname + '_PHONE nvarchar(15);'
               
                --set counters and string               
                SELECT @COLCOUNT = @COLCOUNT - 1
                SELECT @colstring = CASE WHEN LEN(@colstring) > 0 THEN @colstring + '; ' + @colname ELSE @colname END
        END
END

PRINT @SQL

--RUN THE SQL STATEMENT
EXEC SP_EXECUTESQL @SQL

---