InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 20:06:53 GMT20http://www.sqlservercentral.com/Forums/Topic779294-149-1.aspxHello,I require a SQL statement such as the following, this one works but takes 18 or more seconds to complete. It gets held up on the EXECUTE statement. Ideally, If the cursor could somehow get the field value also I wouldn't need the execute statement. the SP --GetFieldValue below didn't have any performance gains either.Any ideas or thoughts would be greatly appreciated, if you need more info let me knowALTER PROCEDURE [dbo].[spCreateNewRecordHistory]( @EntityID INT, @RecordID INT, @WhoCreated nvarchar(100))ASBEGIN SET NOCOUNT ON DECLARE @FieldName nvarchar(100) DECLARE @Value nvarchar(max) DECLARE @Object nvarchar(max) DECLARE @SQL nvarchar(4000) DECLARE @ParmDefinition nvarchar(100) DECLARE @EntityName varchar(100) DECLARE @EntityViewName varchar(100) DECLARE @Change varchar(200) DECLARE @Version INT DECLARE @Result nvarchar(max) SELECT @EntityName = Name, @EntityViewName = BaseView FROM Entity WHERE ID = @EntityID DECLARE crObject CURSOR FAST_FORWARD For SELECT '<FIELD NAME="'+Column_name+'"', Column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE (TABLE_NAME = @EntityViewName) AND Column_name <> 'ID' OPEN crObject FETCH NEXT FROM crObject INTO @Object , @FieldName-- SET @ParmDefinition = N'@pFieldName nvarchar(100), @pEntityViewName nvarchar(100), @ValueOUT nvarchar(200) OUTPUT' SET @ParmDefinition = N'@pFieldName nvarchar(100), @pEntityViewName nvarchar(100), @ValueOUT nvarchar(200) OUTPUT' SET @Result = '<OBJECT CLASS="'+@EntityName+'" ID="'+CAST(@RecordID as varchar)+'" FULL="FULL" VERSION="1">' WHILE @@FETCH_STATUS = 0 BEGIN --SET @SQL = N'SELECT @ValueOUT = @pFieldName FROM '+ @EntityViewName +' WHERE ID='+CAST(@RecordID as varchar) SET @SQL = N'SELECT @ValueOUT = '+@FieldName+' FROM '+ @EntityViewName +' WHERE ID='+CAST(@RecordID as varchar) EXECUTE sp_executesql @SQL, @ParmDefinition, @pFieldName = @FieldName, @pEntityViewName = @EntityViewName, @ValueOUT=@Value OUTPUT --EXEC dbo.spGetFieldValue @EntityViewName, @FieldName, @RecordID, @Value OUTPUT IF ISNULL(@Value,'') <> '' SET @Result = @Result + @Object+'>' + @Value +'</FIELD>' ELSE SET @Result = @Result + @Object + ' />' FETCH NEXT FROM crObject INTO @Object , @FieldName END -- WHILE SET @Result = @Result + '</OBJECT>' SET @Change = '<cs Entity="'+@EntityName+'" ID="-1"/>' INSERT INTO EntityRecordVersions (EntityID ,RecordID, Changes, Comments, RecordData, DateUpdated, WhoUpdated, Type) VALUES (@EntityID ,@RecordID ,@Change ,NULL ,@Result ,GETDATE() ,SUSER_NAME(),1) CLOSE crObject DEALLOCATE crObjectENDFri, 28 Aug 2009 13:44:53 GMTcdb363