SQL Query

Posted on 2009-02-20
Last Modified: 2012-05-06
I have a table that controls user security for an application.  Currently, if a user needs access to more than one department, I am entering multiple records for the user.  I would like to change that model to use just one row.  For example currently USER 1 might have two records in the security table.

USER                               DEPT
User 1                               010
User 1                               020

I would like to condense that into something more like:

USER                             DEPT
User 1                            '010','800'

and pull data based on one record, but am having difficulty.
declare @id varchar(255)

set @id = (SELECT dept from users where user_id = suser_sname())

print @id -- this pulls '010','800'

select * from test_budget

where dept IN ('010','800')-- this pulls the correct information

select * from test_budget 

where dept IN (@id) -- this returns nothing

Open in new window

Question by:gdspeare
    LVL 25

    Accepted Solution

    IN operator will not scan a literal value with embedded commas - you should use dynamic SQL like -

    exec ('select * from test_budget where dept in (''' + @id + ''')')

    You are better off using your current model as it is easier to manipulate set based data using SQL DML..

    LVL 39

    Expert Comment

    by:Roger Baklund
    You should not do this. The DEPT column is normalized, if you put multiple values into the same row you violate the first normal form, it will only give you problems later.
    LVL 22

    Expert Comment


    I second reb73 in that you should keep your current model.

    But I think that if you move to the new model, the solution for you is to do this:

    select * from test_budget where @id LIKE '%'' + dept  + '''%'

    LVL 39

    Expert Comment

    by:Roger Baklund

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now