Variable reference to a column in MS SQL

[DCSS]

I have a table with one record per certification a user has. So, user SMITH can have multiple records, one for DMV another for CPT and another for ICD. SMITH has three certificates but can have up to 20.

The desire is to have a second table with one record per user with columns for each certification indicating with Y or N whether the user has that certificate. The column names are the same as the certificate name (eg. DMV, CPT, ICD,...)

So, is there a way to read in the first table, use the data in the certificate field (DMV, CPT, ICD, etc) to then reference the column in the second table and update the respective column to a "Y" to indicate the user has that certificate? For example: if table2:ColumnName(DMV)=table1:Certificate("DMV") then update table2:Column(DMV)="Y"

The input file we use to create table 1 is in the one record per certificate per user design. We do not control that.

The intent is to avoid long Case statements. If this works, we have another similar application with a possible 130 roles a user can have. I've Googled this multiple ways and read lots of possibilities but none seem to indicate this can be done or I just don't understand it.

Can I do this? If so, how? Any help will be much appreciated. Thank you!

[buddylee17]

Well, lets see.

Many users can be associated with a certificate
and
Many certificates can be associated with a user

This is simply a many to many relationship.

Here's a suggestion:

CREATE TABLE Users(
userid int identity not null primary key, username varchar(25) unique not null
)
CREATE TABLE Certs(
--use smallint or int for certid if you need to support more than 255 certs
certid tinyint identity not null primary key, certification varchar(10) unique not null
)
CREATE TABLE CertUserLink(
--certified column represents the Y/N value. 1 is true, 0 is false
userid int not null, certid tinyint not null, certified bit not null default 0
)

ALTER TABLE CertUserLink
   ADD CONSTRAINT PK_certuserlink PRIMARY KEY CLUSTERED (certid,userid)    
   
--Make sure that the userid and certid are valid before inserts can be made
ALTER TABLE CertUserLink
   ADD CONSTRAINT FK_certs FOREIGN KEY (certid) REFERENCES Certs(certid)
ALTER TABLE CertUserLink
   ADD CONSTRAINT FK_users FOREIGN KEY (userid) REFERENCES Users(userid)

[DCSS]

Thanx buddylee17. I am seeing what I can do with your suggestion. The input file is already formatted for us.

However, based on the research I've done I came up with the following code. According to a couple of forums this should work. The problem is that @colname gets set to "Yes" while a want the column referenced by @colname updated to "Yes". Otherwise, the code works. Here's my code, what am I doing wrong or misunderstanding?

Declare @colname varchar(200), @Eid varchar(50),@message varchar(80),@command varchar(200)
Declare my_cursor CURSOR

For Select replace(replace(replace(SecurityRole,' ',''),'/',''),'-','') as SecRole,EmployeeID
from EmergencyContact.dbo.CSEEmployeeRoles
where EmployeeID='38'
order by EmployeeID, SecRole

open my_cursor
fetch next from my_cursor into @colname,@Eid 
while @@fetch_status = 0
begin
select @message = @colname+' '+@Eid
print @message

select @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = "Yes"
where EmployeeID = '+@Eid
exec (@command)
fetch next from my_cursor into @colname,@Eid
end
close my_cursor
deallocate my_cursor

[DCSS]

I got it to work and thought I would add the code here in case it can help others. Being new to dynamic SQL and Cursor, I was not sure this would work. Once I realized it would, proper use of quotes fixed my problem. :icon_biggrin:

Declare @colname varchar(200), @Eid varchar(50),@message varchar(80),@command varchar(200)
Declare my_cursor CURSOR

For Select replace(replace(replace(SecurityRole,' ',''),'/',''),'-','') as SecRole,EmployeeID
from EmergencyContact.dbo.CSEEmployeeRoles
order by EmployeeID, SecRole

open my_cursor

fetch next from my_cursor into @colname,@Eid 
while @@fetch_status = 0
begin
select @message = @colname+' '+@Eid
--print @message

select @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = ''Yes'',Updated = getdate()
where EmployeeID = '+@Eid
exec (@command)
--print @colname
--print @command
fetch next from my_cursor into @colname,@Eid
--print @colname
end
close my_cursor
deallocate my_cursor

[buddylee17]

Here's my constructive criticism: You tried something new and played with it until it worked. It accomplished your goals, and hopefully you learned alot. Congrats.

In the future:
Cursors are a last resort. They are slow and don't scale well. Dynamic SQL is also a last resort (and only with the use of sp_executesql). A normalized design, with proper foreign keys, would result in a single, set based update statement that can be cached by the query analyzer and will insure consistent, reliable data.

I hope you don't interpret this the wrong way, as I'm a DBA and these things are red flags to me. It's common for programmers to want to jump into sql and use cursors to iterate through result sets. But, in general, set based operations perform better with a RDBMS:

"Writing a database cursor is like going to the bank and depositing a million dollars, one dollar at a time, using a million tiny deposits. In the set-based solution, you just hand over a million one dollar bills at once. The same work is done (the money is counted and deposited) but the teller will not have to have a separate conversation with you for each piece of paper. The performance difference is obvious, and you are saving 999,999 deposit slips to boot. Typically a set-based solution will perform a magnitude better than an iterative solution and will scale significantly better." Paul Nielsen and Louis Davidson: SQL SERVER MVP DEEP DIVES

[DCSS]

Thanx buddylee17 for your input. I agree with you. This file comes from the state weekly in one form we do not like and I am just reformatting it so it can be available through a Gridview in a format we do like. It is strictly for viewing only and any changes come from the state in their next file when this Cursor is run again. Had we designed this, it would have been a different story and I probably would not have had the pleasure to find out about Cursors and Dynamic SQL.

Thanx again for your advise and Happy New Year!