Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We currently have some reports that query a specific table, but one of our users wants the ordering to be different. I don't want to go through and fix each of the reports, because that's rather a mammoth task, and only one user has a problem here. Is there a way to have SELECT queries on a base table automagically SELECT from a view table instead of the base table based on the user making the query? It would be a bonus if I can also base it on the program making the connection. We're using MSSQL 2008.

ie. SELECT * FROM myTable would instead be SELECT * FROM myView if the user executing the query was [user] and the program making the connection was [reporting software name].

EDIT: Note: The outer reporting software is an old version of Crystal Reports running some massive ugly queries built by the query builder. Some sample output would be:

Regular Guy:    Other Guy: 

 ItemCode        ItemCode       
 AAAAAA745       AAAAAA745
 AAABBB672       AAACCC657
 AAACCC657       BBBAAA766
 BBBAAA766       BBBCCC383
 BBBBBB838       AAABBB672
 BBBCCC383       BBBBBB838
 CCCBBB883       CCCBBB883

As we want to order all items with SUBSTRING(ItemCode, 4,3) = 'BBB' at the bottom.

share|improve this question
2  
If they want a specific ordering, why aren't they specifying it in the ORDER BY clause? Please tell me the rest of your users are relying on the "default" order that results from SELECT * FROM table with no ORDER BY clause - this ordering is coincidental and if you don't tell SQL Server how you want the results ordered, it is free to choose any ordering it wants. While today you might be getting the order you want, you might not tomorrow - or next week - or next month. Statistics updates, service packs, hotfixes, even a RECONFIGURE can change the plan that is currently being used. –  Aaron Bertrand Jun 12 '12 at 0:32
    
@AaronBertrand The reporting software seems to be ordering the data - which I realise could throw up some problems in itself. It has a setting to change this to an ORDER BY clause, but it won't fix the root problem. The reason they're not specifying it in an ORDER BY clause is because they want to have a set of item codes appearing together, based on a character sequence in the middle of the string. The reporting software would do this, but I would have to go through every report to change it, and it would be a rather tedious task. –  Hotchips Jun 12 '12 at 0:54
1  
Why do you think referring to a different view name will fix this? You can't rely on ordering that comes out of SQL Server unless you use an ORDER BY clause. –  Aaron Bertrand Jun 12 '12 at 0:57
    
The view name would select SUBSTRING(field, x,y) + SUBSTRING(field, 0, z) AS name or name them differently or somesuch. The report ordering would do the rest (and hopefully not break anything... I wish). –  Hotchips Jun 12 '12 at 1:18
    
So the outer query is already ordering by name, and this guy just wants to order by a subset of that name? To prevent a lot of confusion and spinning wheels, can you show a few rows of sample output for this guy, compared to what all the others are getting, and the actual order by in the queries that should inherit this new ordering just for this user? Also, is the reporting software using SELECT * and adapting to the shape of the resultset? E.g. if you add a column to the output, will it show up on the report? Can you rename one of the existing columns in the output? –  Aaron Bertrand Jun 12 '12 at 1:20
show 5 more comments

2 Answers 2

up vote 1 down vote accepted

You can check these things in sys.dm_exec_sessions, but you have to do it in your (let's hope) stored procedures (you can't redirect a query that specifies a specific table name to a different table based on these variables):

ALTER PROCEDURE dbo.report
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS 
  (
    SELECT 1 FROM sys.dm_exec_sessions 
      WHERE program_name = 'program' 
      AND login_name = 'user'
      AND session_id = @@SPID
  )
  BEGIN
    -- do it their way
    RETURN;
  END

-- do it the normal way
END
GO

This is a bad solution though. If one user wants custom ordering, what happens when another user decides they, too, want different ordering? It will happen. Now all your reports are going to have three forks? Why not make ORDER BY a part of the interface?

I'm wondering if you could do something here with schemas, for example if this guy has a different default schema you could write the query such that it doesn't reference a schema. Everyone else will get dbo.viewname and he would get newschema.viewname. But this is messy, the opposite of self-documenting, and if you're going to write code that handles his case, you may as well fix the code the right way.

Plus, as I suggested in my comment, simply referencing a different view or table name doesn't guarantee you'll get a different ordering by doing so. SQL Server still only has to obey a specific order if you've actually stated one in the outer ORDER BY.

share|improve this answer
    
Stored procedures would definitely make this easier, I agree. Unfortunately, they are simple SELECT queries straight onto the DB. Your default schema plan would, I think, work. You're right though, I should just put in the work and fix the report to use stored procedures. (BTW, it's Crystal Reports being used by people that know nothing about Crystal Reports beyond the Refresh and Print buttons. If I could change the interface, I definitely would (and have, for certain reports)). –  Hotchips Jun 12 '12 at 1:26
    
I don't think the different schemas will magically help, unless you create two different views, add a column to the output that is based on the user name, and change the outer query to order by that column. –  Aaron Bertrand Jun 12 '12 at 1:29
add comment

You could make the view add a ROW_NUMBER with different ORDER BY for different users and then the outer select (or your reporting tool's sort setting) would always (explicitly) sort by that new ORDERBYTHIS column.

i.e. make the view as:

SELECT columns, ROW_NUMBER() OVER(ORDER BY special columns)
FROM whatever
WHERE -- user = special user 
UNION ALL
SELECT columns, ROW_NUMBER() OVER(ORDER BY regular columns)
FROM whatever
WHERE -- user <> special user

http://msdn.microsoft.com/en-us/library/ms186734.aspx

You would still need to set each report to ORDER BY this new column.

Probably not a great performer, but who knows without checking the execution plans.

share|improve this answer
    
I didn't know about this, thanks! Still means I have to go through and change the data source for all the reports though. I think that's going to happen anyway though. sigh –  Hotchips Jun 12 '12 at 2:09
    
@Hotchips Which is why you never let apps connect directly to tables (or even views). It doesn't let you refactor within the database. –  Cade Roux Jun 12 '12 at 2:30
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.