Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Getting a @Tables @FieldName and @FieldValue...
Add to briefcase

Getting a @Tables @FieldName and @FieldValue into a cursor Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
cdb363
Posted Friday, August 28, 2009 1:44 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 18, 2010 11:18 AM
Points: 2, Visits: 45
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 = ''

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 +''
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
Post #779294
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.