3

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
Improve this question
3
  • 1
    Why aren't you using the built-in SQL Agent Jobs functionality? Why reinvent the wheel?? Commented May 26, 2010 at 6:09
  • Not sure that its anything to do with SQL Agent? Might be a work scheduling system. (jobs for client) Commented May 26, 2010 at 6:45
  • We get dictation file from physicians and create a job out of it. Commented May 26, 2010 at 8:31

3 Answers 3

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

+1 Agree on points 1 and 3. It would cache the query plan for dynamic SQL as well though. @Rohit to turn things around what does your coworker say the advantages of their approach are?
He says that its predefined data.So i have stored it this way. There are predefined status for a job in system.These wont change, so whynot store these IDs and use them as where clause.
Right so basically sounds like there is no good reason for doing it at all. In terms of why not. Performance, maintenance, adding unneeded complexity all spring to mind immediately. Additionally if you have them stored in the database in the traditional way then knocking up an admin screen to configure the permissions or using them in any other queries is much simpler.
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
Improve this answer

Comments

0

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...

Improve this answer

1 Comment

Strongly disagree. There is a standard way of doing this in SQL if you depart from it then I would suggest that the onus is on you to have a good reason why.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.