Selecting the field name if a condition is met
I'd like to be able to select the field name from a table if a boolean condition is met. I've got a users table which has a userID field, plus a series of 1/0 tinyint fields to control the display of certain dashboard items. If the field is set to 1, I'd like this query to return the field name.
The stored procedure that I have so far is:
SELECT userShowProcess, userShowLEN, userShowContractExp, userShowWO, userShowTSO, userShowOcc, userShowRev, userShowDelinq, userShowExp
FROM tblUsers
WHERE userID = @UserID;
That gives me 9 columns with each set to a 1 or 0. While this works at the moment, my problem arises if I need to add options to the system -- there would be quite a bit of rewriting on the code side. Plus, I currently have to iterate the recordset (which I've saved into an array) to weed out the zeroes. I've looked into syscolumns and sysobjects but I really don't know what I'm looking at when I see examples of these. My ideal query would show something like this, assuming the user has "1" in four of the 9 fields:
The stored procedure that I have so far is:
SELECT userShowProcess, userShowLEN, userShowContractExp, userShowWO, userShowTSO, userShowOcc, userShowRev, userShowDelinq, userShowExp
FROM tblUsers
WHERE userID = @UserID;
That gives me 9 columns with each set to a 1 or 0. While this works at the moment, my problem arises if I need to add options to the system -- there would be quite a bit of rewriting on the code side. Plus, I currently have to iterate the recordset (which I've saved into an array) to weed out the zeroes. I've looked into syscolumns and sysobjects but I really don't know what I'm looking at when I see examples of these. My ideal query would show something like this, assuming the user has "1" in four of the 9 fields:
itemName
--------
userShowProcess
userShowLEN
userShowWO
userShowRev
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I had discounted that early on and decided to put the values in the users table, but now I have no idea why I did that. This does make more sense.
>> If the field is set to 1, I'd like this query to return the field name.<<
So do you want only the mentioned columns in the SELECT clause with the boolean value set to 1.
SELECT userShowProcess,userShowLE
FROM tblUsers
WHERE userID = @UserID AND BooleanField = 1
If my understanding is wrong then provide some sample set and what exactly you are looking for?