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.
You can use dynamic SQL to create such a view:
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
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.
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.