Need help in SQL query.... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help in SQL query....

Author

Topic

thiruna
Starting Member

41 Posts

Posted - 2002-06-20 : 10:20:21

Hi,
I have a query as follows...

SELECT * from lessons
WHERE department_no = ISNULL (@dept_no, department_no)

Actually this searches and show all lessons from all departments if we supply a NULL valve.

Here is my question...

I have a database with tables
1. Users : userid, username
2. Lessons : lesson_id, lesson_desc, dept_id
3. department : dept_id, dept_name
3. users_dept : user_id, dept_id

Note : A user can belong to many departments

If a user searches for the lessons (Thru' ASP page) and Supply a NULL valve for the department...Then our Stored procedure should search for the lessons only in the dept he belongs to rather than all the departments...

but the following query string searches in all departments...

SELECT * from lessons
WHERE department_no = ISNULL (@dept_no, department_no)

Please give me a solution.

Also if the user tries to search lessons in other departments which he is not belonging to, then our Stored procedure should return a error

Please help me....

Many thanks in advance
thiruna@vsnl.com

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-06-20 : 10:41:31

Re the error: You can use RAISEERROR to raise your own custom error, or you can use RETURN 0 and check for this value in your front end code.
Re the query:
IF @Dept_No IS NULL
SELECT * from lessons
WHERE department_no IN(select dept_id from users_dept inner join users on userid = user_id where username = @username)
ELSE
SELECT * from lessons
WHERE department_no IN(select dept_id from users_dept inner join users on userid = user_id where username = @username and dept_no = @dept_no)
If @@Rowcount = 0 --User doesn't belong here
Error processing

HTH,

Sarah Berger MCSD

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-20 : 11:45:21

I need to say first that your psuedo-DDL does not match your provided query....is it 'department_no' or 'dept_id'?

I don't think you need the subquery or the if...else logic to do this.

example:


if not exists (select 1
               from
                   users_dept
               where
                   user_id = @userid and
                   dept_id = @deptid)
begin
   --error handle code: RAISERROR maybe
end

select
    <column list not *>
from
    lessons l
    inner join users_dept ud
        on l.dept_id = ud.dept_id
where
    ud.userid = @userid and
    ud.dept_id = isnull(@dept_no,dept_id)

Getting rid of the flow control and the subquery may result in a more accurate cached execution plan and better index utilization....

<O>

Edited by - Page47 on 06/20/2002 11:46:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources