MSSQL - How can I reference a column to update using a variable??
-
I have a table with one record per security role a user has. So, user SMITH can have multiple records, one for DMV another for CPT and another for ICD. In this example, SMITH has three security roles but can have up to 100. The desire is to have a second table with one record per user with columns for each security role indicating with Y or N whether the user has that role. The column names are the same as the security role name (eg. DMV, CPT, ICD,...) Smith would have 3 columns with a Y and all the rest with an N. So, is there a way to read in the first table, use the data in the security role 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 role? For example: if table2:ColumnName(DMV)=table1:Security Role("DMV") then update table2:Column(DMV)="Y". The key to both tables is the Employee ID. The input file we use to create table 1 is in the one record per Security Role per user design. We do not control that. The intent is to avoid long Case statements. I've Googled this multiple ways and read lots of possibilities but none seem to have a workable solution but most likely I just don't understand it. Below is one way I hoped would worked and a couple of forums indicated it would but I get 'Yes' in the @colname variable.
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_cursorAny help will much appreciated. Seems so simple. :confused:
-
I have a table with one record per security role a user has. So, user SMITH can have multiple records, one for DMV another for CPT and another for ICD. In this example, SMITH has three security roles but can have up to 100. The desire is to have a second table with one record per user with columns for each security role indicating with Y or N whether the user has that role. The column names are the same as the security role name (eg. DMV, CPT, ICD,...) Smith would have 3 columns with a Y and all the rest with an N. So, is there a way to read in the first table, use the data in the security role 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 role? For example: if table2:ColumnName(DMV)=table1:Security Role("DMV") then update table2:Column(DMV)="Y". The key to both tables is the Employee ID. The input file we use to create table 1 is in the one record per Security Role per user design. We do not control that. The intent is to avoid long Case statements. I've Googled this multiple ways and read lots of possibilities but none seem to have a workable solution but most likely I just don't understand it. Below is one way I hoped would worked and a couple of forums indicated it would but I get 'Yes' in the @colname variable.
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_cursorAny help will much appreciated. Seems so simple. :confused:
That's the wrong way to go about it, so if it has been mandated, I recommend that you find a better company to work for. What you want is a many-to-many relationship between users and roles, this is done with a table that contains the IDs of the user and role -- two IDs per record (maybe with a timestamp of when the relationship was created).
User
1 Smith
2 JonesRole
1 DMV
2 CPTUserRole
1 1 <-- Indicates Smith is in role DMV
1 2 <-- Indicates Smith is in role CPTThis is a much more flexible design that will expand more easily as additional roles are added. P.S. It also allows for quicker querying to see which users are members of a given role.
JTRizos wrote:
the respective column to a "Y"
If you must go that way, I strongly recommend that you use numeric codes -- and a translation table with referential integrity. You don't want invalid values showing up in there.
-
I have a table with one record per security role a user has. So, user SMITH can have multiple records, one for DMV another for CPT and another for ICD. In this example, SMITH has three security roles but can have up to 100. The desire is to have a second table with one record per user with columns for each security role indicating with Y or N whether the user has that role. The column names are the same as the security role name (eg. DMV, CPT, ICD,...) Smith would have 3 columns with a Y and all the rest with an N. So, is there a way to read in the first table, use the data in the security role 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 role? For example: if table2:ColumnName(DMV)=table1:Security Role("DMV") then update table2:Column(DMV)="Y". The key to both tables is the Employee ID. The input file we use to create table 1 is in the one record per Security Role per user design. We do not control that. The intent is to avoid long Case statements. I've Googled this multiple ways and read lots of possibilities but none seem to have a workable solution but most likely I just don't understand it. Below is one way I hoped would worked and a couple of forums indicated it would but I get 'Yes' in the @colname variable.
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_cursorAny help will much appreciated. Seems so simple. :confused:
Keep the table you're having. Storing the same data in more than one place is against one of the fundamental principles of relational databases. And if you really need to have that second table you should read up on pivot.[^] Then consider creating that pivot table as a view. That's one of the purposes of views.
"When did ignorance become a point of view" - Dilbert
-
That's the wrong way to go about it, so if it has been mandated, I recommend that you find a better company to work for. What you want is a many-to-many relationship between users and roles, this is done with a table that contains the IDs of the user and role -- two IDs per record (maybe with a timestamp of when the relationship was created).
User
1 Smith
2 JonesRole
1 DMV
2 CPTUserRole
1 1 <-- Indicates Smith is in role DMV
1 2 <-- Indicates Smith is in role CPTThis is a much more flexible design that will expand more easily as additional roles are added. P.S. It also allows for quicker querying to see which users are members of a given role.
JTRizos wrote:
the respective column to a "Y"
If you must go that way, I strongly recommend that you use numeric codes -- and a translation table with referential integrity. You don't want invalid values showing up in there.
I agree. However, the file comes from the state and we don't control it's format. Otherwise, it would be as you describe. We currently take the file into SQL table and allow access to supervisers and managers through an ASP.NET application using a GridView which provides some functions for the user. So, it's User1 SecRole1 User1 SecRole2 User1 SecRole10 User1 SecRole23 User2 SecROle1 User2 SecRole4 User2 SecRole20 User2 SecRole100 What they want is SecRole1 SecRole2 SecRole3 SecRole4 ... SecRole10 ... SecRole20 SecRole21 SecRole22 SecRole23 ... SecRol100 User1 Y Y N N Y N N N Y N User2 Y N N Y N Y N N N Y in GridView making it easier to compare who is in a SecRole. Thanx for your response. I appreciate any feedback provided.
-
Keep the table you're having. Storing the same data in more than one place is against one of the fundamental principles of relational databases. And if you really need to have that second table you should read up on pivot.[^] Then consider creating that pivot table as a view. That's one of the purposes of views.
"When did ignorance become a point of view" - Dilbert
I thought about a PIVOT but the users have different security roles so I don't think it is possible to show what roles a user has or does not have so users can be compared. I've created PIVOTS in Excel and can't see how I can inidcate whether a user has or does not have a role and then compare them to other users. I will, however, look into this and see if it will work for this application. Thank you for your response. I appreciate any responces I can get.
-
I thought about a PIVOT but the users have different security roles so I don't think it is possible to show what roles a user has or does not have so users can be compared. I've created PIVOTS in Excel and can't see how I can inidcate whether a user has or does not have a role and then compare them to other users. I will, however, look into this and see if it will work for this application. Thank you for your response. I appreciate any responces I can get.
Case When NULL Then 'N' Else 'Y' End
"When did ignorance become a point of view" - Dilbert
-
Case When NULL Then 'N' Else 'Y' End
"When did ignorance become a point of view" - Dilbert
-
Not sure I understand. Also, can I use a PIVOT to create a table available through a GridView or would it be just for reporting? Thanx again!
This is a simpler way to make a crosstab:
Select UserID
,Max(Case When RoleID = 'SecRole1' Then 'Y' Else 'N' End) as SecRole1
,Max(Case When RoleID = 'SecRole2' Then 'Y' Else 'N' End) as SecRole2
,Max(Case When RoleID = 'SecRole3' Then 'Y' Else 'N' End) as SecRole3
,Max(Case When RoleID = 'SecRole4' Then 'Y' Else 'N' End) as SecRole4
,...
From UserRoles
Group By UserIDSQL is for datacollection, it doesn't care whether you use the data for reporting or grids...
"When did ignorance become a point of view" - Dilbert
-
This is a simpler way to make a crosstab:
Select UserID
,Max(Case When RoleID = 'SecRole1' Then 'Y' Else 'N' End) as SecRole1
,Max(Case When RoleID = 'SecRole2' Then 'Y' Else 'N' End) as SecRole2
,Max(Case When RoleID = 'SecRole3' Then 'Y' Else 'N' End) as SecRole3
,Max(Case When RoleID = 'SecRole4' Then 'Y' Else 'N' End) as SecRole4
,...
From UserRoles
Group By UserIDSQL is for datacollection, it doesn't care whether you use the data for reporting or grids...
"When did ignorance become a point of view" - Dilbert
Not being able to get the @colname variable to work, here's what I've now been working on. If it works, there will be 100+ case statements. Will what you recommend above work with an Update? My code seems to go through the table being updated once rather than through the CSEEmployeeRoles table which has the multiple records per user. I've done things like this in SQL reports before but never had this problem.
update table1 set
AuditView = case when table2.SecurityRole='Audit View' and AuditView='No' then 'Yes' else AuditView end,
BasicArchive = case when table2.SecurityRole='Basic Archive' then 'Yes' else BasicArchive end,
BasicModify = case when table2.SecurityRole='Basic Modify' then 'Yes' else BasicModify end,
BasicView = case when table2.SecurityRole='Basic View' then 'Yes' else BasicView end,
CentralScanOperations = case when table2.SecurityRole='Central Scan Operations' then 'Yes' else CentralScanOperations end,
CreateorMaintainParticipant = case when table2.SecurityRole='Create or Maintain Participant' then 'Yes' else CreateorMaintainParticipant end,
CreateRefundParticipant = case when table2.SecurityRole='Create Refund Participant' then 'Yes' else CreateRefundParticipant end,from EmergencyContact.dbo.CSEEmployeeRoles as table2 right join EmergencyContact.dbo.CSERolesRolledUp as table1
on (table2.EmployeeID = table1.EmployeeID)Thanx again, Jörgen, for your input.
-
I agree. However, the file comes from the state and we don't control it's format. Otherwise, it would be as you describe. We currently take the file into SQL table and allow access to supervisers and managers through an ASP.NET application using a GridView which provides some functions for the user. So, it's User1 SecRole1 User1 SecRole2 User1 SecRole10 User1 SecRole23 User2 SecROle1 User2 SecRole4 User2 SecRole20 User2 SecRole100 What they want is SecRole1 SecRole2 SecRole3 SecRole4 ... SecRole10 ... SecRole20 SecRole21 SecRole22 SecRole23 ... SecRol100 User1 Y Y N N Y N N N Y N User2 Y N N Y N Y N N N Y in GridView making it easier to compare who is in a SecRole. Thanx for your response. I appreciate any feedback provided.
There should be no reason to store the data in the same form you receive it.
-
I have a table with one record per security role a user has. So, user SMITH can have multiple records, one for DMV another for CPT and another for ICD. In this example, SMITH has three security roles but can have up to 100. The desire is to have a second table with one record per user with columns for each security role indicating with Y or N whether the user has that role. The column names are the same as the security role name (eg. DMV, CPT, ICD,...) Smith would have 3 columns with a Y and all the rest with an N. So, is there a way to read in the first table, use the data in the security role 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 role? For example: if table2:ColumnName(DMV)=table1:Security Role("DMV") then update table2:Column(DMV)="Y". The key to both tables is the Employee ID. The input file we use to create table 1 is in the one record per Security Role per user design. We do not control that. The intent is to avoid long Case statements. I've Googled this multiple ways and read lots of possibilities but none seem to have a workable solution but most likely I just don't understand it. Below is one way I hoped would worked and a couple of forums indicated it would but I get 'Yes' in the @colname variable.
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_cursorAny help will much appreciated. Seems so simple. :confused:
Both Piebald and Jorgen have essentially answered your question You MUST not store the data again (unless you are going to normalise the data as suggested by piebald) You should use a pivot query to present the data in the format the user requires. This article [^]may help (shameless plug) I would suggest that you do both, restructure the data when you receive it from the State, just b/c someone else has a crappy format (or you are actually receiving denormalised data designed for output) does not mean you can't change it in your loading process. Create a view (or at least a stored proc) based on the article that supplies the data in the format the users require. Oh and give Piebald and Jorgen an upvote for the valid answers.
Never underestimate the power of human stupidity RAH
-
Both Piebald and Jorgen have essentially answered your question You MUST not store the data again (unless you are going to normalise the data as suggested by piebald) You should use a pivot query to present the data in the format the user requires. This article [^]may help (shameless plug) I would suggest that you do both, restructure the data when you receive it from the State, just b/c someone else has a crappy format (or you are actually receiving denormalised data designed for output) does not mean you can't change it in your loading process. Create a view (or at least a stored proc) based on the article that supplies the data in the format the users require. Oh and give Piebald and Jorgen an upvote for the valid answers.
Never underestimate the power of human stupidity RAH
Shameless plug. I think not. It totally relevant to the OP. If I hadn't forgotten that I already bookmarked it, I would have linked to it myself. :sigh: I guess that's just how bad your brain works when you're home with the flu.
"When did ignorance become a point of view" - Dilbert
-
Both Piebald and Jorgen have essentially answered your question You MUST not store the data again (unless you are going to normalise the data as suggested by piebald) You should use a pivot query to present the data in the format the user requires. This article [^]may help (shameless plug) I would suggest that you do both, restructure the data when you receive it from the State, just b/c someone else has a crappy format (or you are actually receiving denormalised data designed for output) does not mean you can't change it in your loading process. Create a view (or at least a stored proc) based on the article that supplies the data in the format the users require. Oh and give Piebald and Jorgen an upvote for the valid answers.
Never underestimate the power of human stupidity RAH
You are right, both their responses were very helpful and I am now working on using a Pivot to do this. Not sure yet how the end result can be used by a GridView to display the data to the user. Just opened your article and seems to be really helpful. Thanx! However, my original question had to do with using a variable to reference a column for updating. From what I've read, this should work ... as I understand it. Sadly, it does not for me. So, final question ... is this doable and if it is, what am I doing wrong? See original question and code. :confused: Thanx again and I will give upvotes to both. :thumbsup:
modified on Tuesday, December 21, 2010 12:11 PM
-
You are right, both their responses were very helpful and I am now working on using a Pivot to do this. Not sure yet how the end result can be used by a GridView to display the data to the user. Just opened your article and seems to be really helpful. Thanx! However, my original question had to do with using a variable to reference a column for updating. From what I've read, this should work ... as I understand it. Sadly, it does not for me. So, final question ... is this doable and if it is, what am I doing wrong? See original question and code. :confused: Thanx again and I will give upvotes to both. :thumbsup:
modified on Tuesday, December 21, 2010 12:11 PM
The only way to do it is the one you have used, dynamic sql.
Never underestimate the power of human stupidity RAH
-
The only way to do it is the one you have used, dynamic sql.
Never underestimate the power of human stupidity RAH
-
But it does not work and I am stumped. Thanx for your help. I am working through your Pivot article too see if it applies to what I am trying to do. Good article!
JTRizos wrote:
select @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = "Yes"where EmployeeID = '+@Eid
Print this variable and run it directly in SSMS, see if it updates, I think you problem is simply formatting the string! try changing "Yes" to 'Yes'
Never underestimate the power of human stupidity RAH
-
JTRizos wrote:
select @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = "Yes"where EmployeeID = '+@Eid
Print this variable and run it directly in SSMS, see if it updates, I think you problem is simply formatting the string! try changing "Yes" to 'Yes'
Never underestimate the power of human stupidity RAH
Thanx for the suggestion. Printing the variable @command displays the correct Update command: update EmergencyContact.dbo.CSERolesRolledUp set AddRequesterInformation = "Yes" where EmployeeID = 38 But I get an "Invalid column name 'Yes'" error and no update is done. Feeling a bit more reassured that this will work but need to figure out why the error. At least you did not say this would not work. Thanx again and Merry Christmas!
-
Thanx for the suggestion. Printing the variable @command displays the correct Update command: update EmergencyContact.dbo.CSERolesRolledUp set AddRequesterInformation = "Yes" where EmployeeID = 38 But I get an "Invalid column name 'Yes'" error and no update is done. Feeling a bit more reassured that this will work but need to figure out why the error. At least you did not say this would not work. Thanx again and Merry Christmas!
change the quotes around yes to single quotes '
Never underestimate the power of human stupidity RAH
-
change the quotes around yes to single quotes '
Never underestimate the power of human stupidity RAH
I got it to work. Followed your advise from your previous reply and after a few tries, bingo, it worked. Here's the code in case it can help others. I am leaving the print statements I used for testing.
Declare @colname varchar(200), @Eid varchar(50),@message varchar(80),@command varchar(200)
Declare my_cursor CURSORFor Select replace(replace(replace(SecurityRole,' ',''),'/',''),'-','') as SecRole,EmployeeID
from EmergencyContact.dbo.CSEEmployeeRoles
order by EmployeeID, SecRoleopen my_cursor
fetch next from my_cursor into @colname,@Eid
while @@fetch_status = 0
begin
select @message = @colname+' '+@Eid
--print @messageselect @command= 'update EmergencyContact.dbo.CSERolesRolledUp set '+@colname+' = ''Yes''
where EmployeeID = '+@Eid
exec (@command)
print @UpdDate
--print @colname
--print @command
fetch next from my_cursor into @colname,@Eid
--print @colname
end
close my_cursor
deallocate my_cursorThanx again for your help. Having never used Cursor or Dynamic SQL before, I just needed to know it can be done and I was on the right track. Happy Holidays! :-D