MSSQL - Presenting data when column names dynamic...

Question

I am presenting to a final authority evaluation scores for employees. Each row is an employee’s data and since the categories to be evaluated can change from period to period the column names cannot be hardcoded in the Stored Procedures. I have already devised the following solution.

1 Create a temp table
2 Dynamically use the Alter Table command to add all applicable columns (Stored in @ColumnNames)
3 Use Dynamic SQL inside a cursor to write an insert for each employee that gets the correct scores (IE N employees means N inserts)

(SELECT @ECMScores = COALESCE(@ECMScores + ',', '') + CAST(EIS.ECMScore AS NVARCHAR(1000)) (FROM...))

SET @SQLString = ''

SET @SQLString = @SQLString + 'INSERT INTO  #ResultSet ('
SET @SQLString = @SQLString + 'EvaluationScoreID,'
SET @SQLString = @SQLString + 'EmployeeID,'
SET @SQLString = @SQLString + 'EmployeeName,'
SET @SQLString = @SQLString + @ColumnNames
SET @SQLString = @SQLString + ') '


SET @SQLString = @SQLString + 'VALUES ('
SET @SQLString = @SQLString + ''+CAST(@EvaluationScoreID AS NVARCHAR(MAX))+','
SET @SQLString = @SQLString + ''+CAST(@EmployeeID AS NVARCHAR(MAX))+','
SET @SQLString = @SQLString + '"'+@EmployeeName+'",'
SET @SQLString = @SQLString + @ECMScores
SET @SQLString = @SQLString + ')'

EXECUTE sp_executesql @SQLString

The problem is it takes approx 1 second for every 100 employees. This quickly becomes unacceptable…

Does anyone have any better ideas on how to proceed? Removing the cursor (obviously), and using one insert (Perhaps Select into) is my first idea perhaps reading from a dynamically created XML variable…

Thanks,

Answer 1

First of all, would you have the opportunity to alter the database structure? If I understand correctly, field EIS.ECMScore contains multiple values, which it's a violation of normalization rules. I personally would redesign table EIS and normalize it, as it would make your task much easier.

Answer 2

don't store data in the schema

create tables like this:

Assignment
AssignmentID   int not null primary key identity(1,1)
AssignmentName varchar(50)  not null
AssignmentDate datetime not null
etc..

Score
ScoreID       int not null primary key identity(1,1)
EmployeeID    int not null
AssignmentID  int not null
ScoreValue    int not null

Employee
EmployeeID    int not null primary key identity(1,1)
EmployeeName  varhar(100) not null
etc..

you can now create "assignments" and insert "scores" without building dynamic queries because your columns always change. your columns will now stay the same and the data will change.