0

Currently I have a table that has a user_type column and will only show the row if the user matches that user_type. I want to make it so there can be multiple user_types set without duplicating the data and without creating another table. I thought that I could convert the column from an int to varchar and have it be a comma separated list of user type IDs.

So far it has been working great. That is as long as I know what the user_type is beforehand because then I can specifically use that when checking if I should display to the user:

SELECT *
FROM perm
WHERE user_type='50'
    OR user_type LIKE '50,%'
    OR user_type LIKE '%,50,%'
    OR user_type LIKE '%,50'

The issue comes when I am trying to join tables on the value. When I tried using IN:

SELECT p.*
FROM perm p
JOIN [user] u ON u.type IN (p.user_type)

I get the error: Conversion failed when converting the varchar value '50,40,30' to data type int. So I decided I would go back to the LIKE method which I have been using above:

SELECT p.*
FROM perm p
JOIN [user] u ON (
    u.type LIKE p.user_type
    OR u.type LIKE (p.user_type + ',%')
    OR u.type LIKE ('%,' + p.user_type + ',%')
    OR u.type LIKE ('%,' + p.user_type)
)

This only returns results that have one user type value only. Is there a way to convert a comma separated list to be used with the IN command? Or is there any way to make a dynamic LIKE argument?

Aust
  • 11,552
  • 13
  • 44
  • 74
  • 4
    `I thought that I could convert the column from an int to varchar and have it be a comma separated list of user type IDs` This is a very bad idea. Create another table. Maintaining a comma separated list is an huge hassle. – crthompson May 23 '14 at 21:25
  • What @paqogomez said. As you've found it's difficult to "unroll" the data if done this way. Additionally it negates the DB's strengths (its relational ability). – Alvin Thompson May 23 '14 at 21:28
  • As was already said, this is a bad idea. Anyway, change your `JOIN` condition with: `ON p.user_type LIKE '%,' + u.type + ',%'` – Lamak May 23 '14 at 21:30
  • Indeed. However, in my case I wouldn't need to maintain this comma separated list. Once the user type is set, it will never be changed. So it seems there is no gain in creating a second table. Am I right? – Aust May 23 '14 at 21:32
  • Never is a long time. – crthompson May 23 '14 at 21:33
  • The table is a results only table. So never means never in this case. =) So again I ask, what are the benefits in creating a second table as long as there's a way to do what I initially asked? – Aust May 23 '14 at 21:36
  • Every time you run this query you'll have to parse that string. That's slower. Also as @paqogomez says it's hard to believe the user types will never be updated. – Alvin Thompson May 23 '14 at 22:14
  • I have dozens of reference tables in my database that rarely change. I've even created a template file in my solution to make enums out of them so i can reference them in code w/o strings. They do change tho, its rare, but they change. New requirements come by all the time. You can do what you want, but in 3 months when you post the question "how do I unravel my crappy architecture" you'll think of me. :) – crthompson May 23 '14 at 22:17
  • Yes I think it's better to just use a second relational table after all. I was just hoping that my conversion from our current system to this new system could be a bit faster/easier than changing all of the code to handle a second relational table. But at least this way, my code is not tied to MSSQL only. – Aust May 24 '14 at 01:13

3 Answers3

2

There'e many examples of TSQL code that split separated string into "array" or table variable.

For example using this function: https://stackoverflow.com/a/10914602/961695, you can rewrite your query as:

SELECT p.*
FROM perm p JOIN dbo.splitstring('50,40,30') s
ON p.user_type = s.name

(and avoid dynamic SQL as well)

Community
  • 1
  • 1
Yuriy Galanter
  • 38,833
  • 15
  • 69
  • 136
  • It's not letting me create the function. I'm using SQL Server 2008 R2 Express. Any ideas? – Aust May 23 '14 at 21:47
  • I figured it out. I was using the function like `u.type IN (dbo.splitstring(p.user_type))` but I should've been using it like `u.type IN (SELECT * FROM dbo.splitstring(p.user_type))` – Aust May 23 '14 at 22:09
  • Glad it worked for you. But I still recommend using JOIN instead of IN – Yuriy Galanter May 23 '14 at 23:03
0

I'll give you the way that works with what you have now (horrible) and the right way to do it (as @paqogomez suggested). This way involves abusing PARSENAME, and only works with SQL Server and only if perm.user_type has 4 or fewer user types. There's another way that doesn't have the limit of 4--it involves abusing SQL Server's XML parsing--but it's more complex and slower so I won't show that one:

I also assume you just want to list the rows in Perm for a specific user, and the [user] table has id as it primary key:

SELECT p.*
FROM [user] u
JOIN perm p ON u.type IN (
    CAST((PARSENAME(REPLACE(p.user_type,',','.'),1)) AS INT),
    CAST((PARSENAME(REPLACE(p.user_type,',','.'),2)) AS INT),
    CAST((PARSENAME(REPLACE(p.user_type,',','.'),3)) AS INT),
    CAST((PARSENAME(REPLACE(p.user_type,',','.'),4)) AS INT)
)
WHERE u.id = ?

The better way is the paqogomez way, where you use a relation table to store the user types for Perm (assumes the primary key for Perm is id:

Perm_User_Type
  Perm_id -> Perm.id
  User_type -> [user].type

Then the much more efficient query would look like this:

SELECT p.*
FROM [user] u
JOIN Perm_User_Type put ON u.type = put.User_type
JOIN perm p ON put.Perm_id = p.id
WHERE u.id = ?

Of course there would be no limit on the number of user types in this case.

Alvin Thompson
  • 5,388
  • 3
  • 26
  • 39
  • Very creative. However, there are more than 4 user types. There can be an infinite number of them. – Aust May 23 '14 at 22:10
  • There's no limit on the user types, just a limit on the number of user types a given entry in the Perm table can have. If a given entry in the Perm table can have more than 4 user types associated with it, you'll have to abuse the XML parsing features instead. That's a lot more complex. Do I really have to look it up (I can't remember off the top of my head) or will you **please** do it the right way? – Alvin Thompson May 23 '14 at 22:27
  • Or, you simply make sure that no Perm row has more than 4 users associated with it. At this point the schema/data is no masterpiece anyway. :P – Alvin Thompson May 23 '14 at 22:39
-1

I don't think you can do this without dynamic SQL. Something like: 

declare @sql nvarchar(max)

set @sql = '
SELECT p.*
FROM perm p
JOIN [user] u ON u.type IN (''' + p.user_type + ''')'

exec sp_ExecuteSql @sql
SeeJayBee
  • 1,188
  • 1
  • 8
  • 22
  • This won't work. `p.user_type` is a string that has: `'50,40,30'` inside, and you'll need the dynamic SQL to be: `'50','40','30'`, with single quotes between the commas – Lamak May 23 '14 at 21:31
  • Not sure who downvoted, as it's a pretty good solution. There's at least one way that was pointed out to avoid the dynamic SQL, but it's not like this is a 600 line query where stuff can go wrong relatively easily. – user2366842 May 23 '14 at 21:47