>>Naomi,
>>
>>Actually there is more to that query. It is a part of a stored procedure called from an ASP.NET application. Based on the parameter passed I am dynamically building the actual query.
>>
>>I could not come up with a way of doing this without using Execute.
>>
>>Thanks again for your help,
>>DAniel
>
>Daniel,
>I didn't saw ANY reason why you use Dynamic SQL?
>Your columns are predefined and your Table and Database also is known. You could use this in your SP w/o any troubles
>

>declare @cUserName nvarchar(50)
>set @cUserName = 'danielm'
>select *
>	from (
>		 select a.iIpoID, a.cGroupDealID, b.cIpoStatus, a.dTargetLiveDate, a.cCustNo, a.cCompany,;
 (select cName from ipo_dsiContact where iIpoID = a.iIpoID and cDsiContType = 'IS') as cNameIS
, (select cName from ipo_dsiContact where iIpoID = a.iIpoID and cDsiContType = 'TSS') as cNameTSS
, (select cName from ipo_dsiContact where iIpoID = a.iIpoID and cDsiContType = 'CFA') as cNameCFA
, c.cUserName, c.cPermission, a.dCreate
		 from ipo a
		join lookup_ipoStatus b on a.iIpoStatus = b.iIpoStatus
		join ipo_permission c on a.iIpoID = c.iIpoID
		) q
    where cUserName = @cUserName
>          and Year(dCreate) = 2007
>		  order by q.iIpoId DESC
>

>
>UPDATE: Also that query could be build to be much faster :-)
>Just let me thing a minute

Yes, can be definitely done faster with CASE statement. You don't need to use SELECT in from clause, you just construct names in CASE END, etc.

But I'm not that proficient with T-SQL.

Here is one sample which can be used as a prototype
http://www.berezniker.com/display/SQL/IsNumericEx+UDF+-+Data+type+aware