SQL Query

Posted on 2009-02-20
Medium Priority
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
  • 2
LVL 25

Accepted Solution

reb73 earned 2000 total points
ID: 23697122
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
ID: 23697134
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

ID: 23697141

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  + '''%'


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

862 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