2

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.

  • 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
  • So right now are the Crystal Reports queries explicitly specifying ORDER BY Name, or is this just how you've observed the order happens to "work"? – Aaron Bertrand Jun 12 '12 at 2:13
  • Crystal does it on the client-side explicitly. – Hotchips Jun 12 '12 at 2:15
  • And it's ORDER BY Name? If it's ORDER BY Ordinal you may be able to fool it, but if it explicitly mentions the column name you can't change the ordering and not change the output in that column. – Aaron Bertrand Jun 12 '12 at 2:19
  • Effectively, yes. – Hotchips Jun 12 '12 at 2:21
1

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.

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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