Getting a @Tables @FieldName and @FieldValue into a cursor

• cdb363

  SSC Rookie

  Points: 42

  More actions

  August 28, 2009 at 1:44 pm

  #138513

  Hello,

  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 know

  ALTER PROCEDURE [dbo].[spCreateNewRecordHistory]

  ( @EntityID INT,

  @RecordID INT,

  @WhoCreated nvarchar(100)

  )

  AS

  BEGIN

  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 ' 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 = ''

  ELSE

  SET @Result = @Result + @Object + '>'

  FETCH NEXT FROM crObject

  INTO

  @Object ,

  @FieldName

  END -- WHILE

  SET @Result = @Result + ''

  SET @Change = ''

  INSERT INTO EntityRecordVersions (EntityID ,RecordID, Changes, Comments, RecordData, DateUpdated, WhoUpdated, Type)

  VALUES (@EntityID ,@RecordID ,@Change ,NULL ,@Result ,GETDATE() ,SUSER_NAME(),1)

  CLOSE crObject

  DEALLOCATE crObject

  END