Tutorial :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,


Solution:1

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.


Solution:2

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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »