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