T-SQL: find an empty column

Question

Hi,
In my table there are 10 columns used for storing "items" (game server). When an user picks the item up, it should loop through columns 0-9 (with names "I0".."I9") and returns once the column in current row is empty. I cannot figure out how to do it on the SQL server side. I can only think of IF EXISTS for each column but that is not really good.. Glad for any help! Thanks

Answer 1

Maybe it's better to have table items (user_id, item_id) and limit the count to 10 for each user?

It seems for me to be a good way to normalize your table and solve your problem. Also your table structure becomes more flexible - you can increase or decease the limit dynamically.

Answer 2

It sounds to me like you're trying to find an empty column for a specified user (row). And, an empty column contains NULL. Assuming this is correct and ignoring the concerns about proper design, here's something that will return the number of the first column that contains a NULL value.

SELECT CASE
    WHEN I0 IS NULL THEN 0
    WHEN I1 IS NULL THEN 1
    WHEN I2 IS NULL THEN 2
    WHEN I3 IS NULL THEN 3
    WHEN I4 IS NULL THEN 4
    WHEN I5 IS NULL THEN 5
    WHEN I6 IS NULL THEN 6
    WHEN I7 IS NULL THEN 7
    WHEN I8 IS NULL THEN 8
    WHEN I9 IS NULL THEN 9
  END AS NextColumn
FROM MyTable
WHERE UserID = 'Me'
;