question

I don't know for sure but I'm guessing you simply can't use EXEC within a function. Functions are not allowed to alter the database state (you can't INSERT OR UPDATE within a function for example) and I suppose SQL has no way of knowing what it is you're attempting to EXEC so it's not allowed within a function. As I say - that's a guess and happy to be corrected by others! Could you use a stored procedure instead?

Mj, you're probably right; I will read about that issue a bit more. Using a SP is (I think) not an option : what I really want to achieve is dynamically pivoting : a user adding a new row value, should lead to a column being dynamically added to a view ... A view however, does not support the functionality needed to accomplish this. A Stored Procedure does, but that can not be used in the FROM clause of a select. This is where a Table-Valued-Function would come in handy ... More precisely, its following code I would like to wrap into a Table-Valued-Function : Declare @cols NVARCHAR(MAX); Declare @query AS NVARCHAR(MAX); Set @cols = STUFF((SELECT distinct ',' + QUOTENAME(ProjectProperties.PropertyId) FROM ProjectProperties FOR XML PATH('') , TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') set @query = 'SELECT ProjectId, ' + @cols + ' from ( select ProjectId, PropertyId, PropertyValue from ProjectProperties ) x pivot ( max(PropertyValue) for PropertyId in (' + @cols + ') ) p ' execute(@query) Following script will create the table, should you feel like studying the above script ... CREATE TABLE [dbo].[ProjectProperties] ( [ProjectId] [nvarchar](15) NOT NULL , [PropertyId] [nvarchar](15) NOT NULL , [PropertyValue] [nvarchar](max) NOT NULL ) And some data ... INSERT INTO ProjectProperties VALUES (N'AFG1', N'CB', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG1', N'COOP', N'BILAT') INSERT INTO ProjectProperties VALUES (N'AFG1', N'FINANC', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG1', N'NATION', N'AFG') INSERT INTO ProjectProperties VALUES (N'AFG1', N'PSSM', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG1', N'SURAMMODEST', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG1', N'SURWEAPENDEST', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG1', N'TECH', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG2', N'ASSISTTYPE', N'Financial') INSERT INTO ProjectProperties VALUES (N'AFG2', N'CB', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG2', N'COOP', N'BILAT') INSERT INTO ProjectProperties VALUES (N'AFG2', N'FINANC', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG2', N'FUTUREPRJREQ', N'YES') INSERT INTO ProjectProperties VALUES (N'AFG2', N'NATION', N'AFG') INSERT INTO ProjectProperties VALUES (N'AFG2', N'PSSM', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG2', N'STATUS', N'Ongoing') INSERT INTO ProjectProperties VALUES (N'AFG2', N'SURAMMODEST', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG2', N'SURWEAPENDEST', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG2', N'TECH', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG3', N'COOP', N'MULTI') INSERT INTO ProjectProperties VALUES (N'AFG3', N'NATION', N'AFG') INSERT INTO ProjectProperties VALUES (N'AFG3', N'PSSM', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG3', N'STATUS', N'Completed') INSERT INTO ProjectProperties VALUES (N'AFG3', N'SURAMMODEST', N'-1') INSERT INTO ProjectProperties VALUES (N'AFG3', N'SURWEAPENDEST', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB1', N'COOP', N'MULTI') INSERT INTO ProjectProperties VALUES (N'ALB1', N'FINANC', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB1', N'FUTUREPRJREQ', N'YES') INSERT INTO ProjectProperties VALUES (N'ALB1', N'NATION', N'ALB') INSERT INTO ProjectProperties VALUES (N'ALB1', N'SURWEAPENDEST', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB1', N'TECH', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB2', N'CB', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB2', N'COOP', N'MULTI') INSERT INTO ProjectProperties VALUES (N'ALB2', N'FINANC', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB2', N'NATION', N'ALB') INSERT INTO ProjectProperties VALUES (N'ALB2', N'STATUS', N'Completed') INSERT INTO ProjectProperties VALUES (N'ALB2', N'SURAMMODEST', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB2', N'TECH', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB3', N'CB', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB3', N'COOP', N'BILAT') INSERT INTO ProjectProperties VALUES (N'ALB3', N'FINANC', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB3', N'NATION', N'ALB') INSERT INTO ProjectProperties VALUES (N'ALB3', N'SURAMMODEST', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB3', N'SURWEAPENDEST', N'-1') INSERT INTO ProjectProperties VALUES (N'ALB3', N'TECH', N'-1') Try running the script at the top and look at the result. Next, insert a new row using INSERT INTO ProjectProperties VALUES (N'XXX', N'XXX', N'XXX') and again, run the script at the top and see what happened to the result. This is what I would like to be able to wrap into a function. Garry

Have you considered materialising the view itself and then creating a trigger on the table ProjectProperties and updating the view each time a new row is inserted? It may not be ideal, but would solve your issue and ensure the view is always up to date. Another possibility would be to look at what you are actually doing and try and find another way of solving the problem. The table ProjectProperties certainly looks like an [EAV][1] table, which is in itself not an ideal solution to most problems. [1]: http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model

Hi. Does it need to be dynamic - do you add new PropertyIds often enough that the benefits of having it dynamic outweigh the draw backs of trying to code a solution that allows it to be dynamic? When I've had situations like this I've always created a view using a distinct list of attributes. When there's a new attribute I roll out an updated version of the view. There's an overhead doing it that way, but I think you can usually write nicer code rather than having to use dynamic SQL or triggers etc. Obviously if the creation of new attributes isn't easily controlled or created very frequently the overhead becomes too much or simply unmanageable.

William, Absolutely correct : it IS an EAV table (thanks for the link ;^}) The trigger approach is certainly worth looking at ! The reason for using the Entity-Attribute-Value model is the development iteration we're in : the data model isn't stable yet and we wanted to prove flexibility to the user. Mj, Point taken ! I believe you are right and we should consider redoing the view every time a new property is being added : after all, the UI doesn't get updated automatically either ... Thank you both for sharing your expertise ! Garry