none
When creating a view, execute code found in a table

    Question

  • I use SQL Server 2012; we have a table where the user can save data needed to build a view. This table looks like this:

    terminal selection_code ABC123 AND emp.Employee_Code = 'JOHN'

    We need to create a view that joins the "real" data table with the selections table mentioned above (where the selection criteria is stored). The view should look like this:

    CREATE VIEW new_view AS SELECT emp.* FROM employee_table emp WITH (NOLOCK) INNER JOIN selections_table sel WITH (NOLOCK)  ON sel.terminal = 'ABC123' WHERE emp.Employer = 'Amazon' sel.selection_code

    As you can see, the last line above has to be "dynamic"; in other words, when the view code executes, it should use the current contents of the selections table to display the view records. I'm not sure if this can be achieved, or how.

    Any help would be greatly appreciated.

    Wednesday, August 21, 2013 9:00 PM

Answers

  • You don't want to use a VIEW at all. You need the application to call a stored procedure. 
    • Marked as answer by ShakaDeVirgo Wednesday, August 21, 2013 11:52 PM
    Wednesday, August 21, 2013 9:44 PM
    Moderator

All replies

  • You can use dynamic SQL to create such a view:

    http://www.sqlusa.com/bestpractices/dynamicsql/

    You can also just use a dynamic query.

    With table redesign, regular static SQL can be applied.


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012


    Wednesday, August 21, 2013 9:04 PM
    Moderator
  • Hello, Kalman...

    Thanks for replying to my question. I have visited the suggested page (Dynamic SQL) but I'm having a hard time finding an example that looks like what I need to do. Could you point me in the right direction?

    Thanks again!

    Wednesday, August 21, 2013 9:14 PM
  • You cannot create a view which dynamically reads a table and creates a WHERE clause.  You would need to use a stored procedure and dynamic SQL.

    Wednesday, August 21, 2013 9:17 PM
    Moderator
  • You cannot create a view which dynamically reads a table and creates a WHERE clause.  You would need to use a stored procedure and dynamic SQL.

    Tom:

    If I use a stored procedure, I think that would make the view "unique", right? In other words, if User A executes the stored procedure, the view would be built based on his selection criteria; but if User B comes in later, the stored procedure would then drop and rebuild the view based on the new criteria; am I right?

    In this case, I need the view to be "unique" for each user in the system. In other words, each user would have different contents on the view depending on their own selection criteria.

    Any examples you can give me about how to create this would be really awesome, as I'm not too experienced with stored procedures.

    Thanks!

    Wednesday, August 21, 2013 9:24 PM
  • You don't want to use a VIEW at all. You need the application to call a stored procedure. 
    • Marked as answer by ShakaDeVirgo Wednesday, August 21, 2013 11:52 PM
    Wednesday, August 21, 2013 9:44 PM
    Moderator