Hi,

Actually my function is returning a dynamic query .

Step 1 : Create a function returning property names in CSV format

Create FUNCTION dbo.fxnGetPropertyColsQuery

(

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

declare @Return NVARCHAR(MAX)

select @Return = STUFF((SELECT distinct ',' + QUOTENAME(c.propName)

FROM PropertyDef c where objecttypeid = 3

FOR XML PATH(''), TYPE

).value('.', 'NVARCHAR(MAX)')

,1,1,'')

RETURN @return

End

Step 2: Create a function accepting return value from dbo.fxnGetPropertyColsQuery as a parameter

create FUNCTION dbo.fxnGetPropertiesQuery

(

@cols NVARCHAR(MAX)

)

RETURNS NVARCHAR(MAX)

AS

BEGIN

declare @Return NVARCHAR(MAX)

select @Return =

'Select EquipmentNo,' + @cols + '

From

(

Select EquipmentNo,PropValue,PropName,PropertyDef.PropertyDefID

From PropertyDef Inner Join EPropertyData

ON PropertyDef.PropertyDefID=EPropertyData.PropertyDefID

INNER JOIN Equipment ON Equipment.EquipID=EPropertyData.ObjectID

WHERE ObjectTypeID In(3,4)

)AS SourceTable

PIVOT

(

max(PropValue)

For

PropName In (' + @cols + ')

)AS PivotTable;'

RETURN @return

End

I want to create a view executing function fxnGetPropertiesQuery returning table.