Storing SQL queries in Table in sql server

Question

We have multiple jobs in our system. These jobs are listed in a grid. We have 3 different user types (usertypeid 1,2,3). For each user listing is different and he can filter listing by selecting view from a dropdown. ViewName in the below table is the view which needs to be displayed. To achieve this functionality, a fellow developer has created the following table structure and stored sql fragments in SQLExpression in the below table. According to me the query should not be stored in database. What are the pros and cons of this approach and what are the available alternatives?

JobListingViewID         ViewName        SQLExpression            UserTypeID
 3                     All Jobs            1 = 1                       3
 4                     Error Jobs   JobStatusID IN ( 2 )               1
 5                     Error Jobs   JobStatusID IN ( 2 )               2
 6                     Error Jobs   JobStatusID IN ( 2 )               3
 7                     Speech       JobStatusID IN ( 1, 3, 8 )         1

Answer 1

You just need to have a Matrix table of ViewName UserTypeID JobStatusID combinations and then join onto that in your query. You don't need dynamic SQL and an additional DB lookup.

NB: For the "All Jobs" View you should just have a different query that forgoes the JOIN. This will save a bit of unnecessary processing and mean when you add a new job you don't have to set up the permissions. For the rest use a matrix like the below.

ViewName        JobStatusID        UserTypeID
Error Jobs             2                1
Error Jobs             2                2
Error Jobs             2                3
Speech                 1                1
Speech                 3                1
Speech                 8                1

Answer 2

Disadvantages: 1. Pain to maintain. 2. No optimisation (caching etc). 3. Changing object names means you'll have to change the data in that table. It's ok for the few records, but once the table grows, you'll spend more time maintaining that table.

Answer 3

I guess that fellow developer is right, and that approach, if it works for him (and for the rest of the system) is just as good as any. Rework it later if it will make a problem - performance, maintenance, something...