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.
Best Answer
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):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 getnewschema.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
.