Variable Not Holding Value For Select Statement

this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on (((   @UsersMaxID  ))) but for some reason it will not work with the next select statement...

 

can someone make the pain go away and help me here..??

 

erik..

 

GOSET ANSI_NULLS ON GO

ALTER  PROCEDURE AA

ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200)

SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik'

 DECLARE @SQL NVARCHAR(4000)  DECLARE @UserID INT  DECLARE @UsersMaxID INT  DECLARE @MaxID INT

declare @tempResult varchar (1000)

-------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY,

UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr)

Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr

FROM USERS

 WHERE ' + @GenericColumn +' = ''' + @GenericValue  + ''''

EXEC sp_executesql @SQL

SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)

SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29

SELECT * FROM #UsersTempTable

 

 ==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID)

SELECT SpecialtyName FROM Specialty s                           INNER JOIN UserSpecialty us                           ON s.SpecialtyCD = us.SpecialtyCD                           WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE