When creating a view, execute code found in a table
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 won't be able to do that as a view. A view can't have statements that are unable to be resolved at the time you [create the view]. You wouldn't want users to create hundreds and hundreds of views anyway. You can do this as a stored procedure, but you will have to use dynamic T-SQL. But, you're looking at a pretty high likelihood of [SQL Injection]. If you do go down this path, make sure you use sp_executesql to build the dynamic sql and pass in parameters and parameter values to it when you do. Also, you are aware that NOLOCK can lead to missing or extra rows in a result set, right? Not just dirty values? I'd be very cautious using that. :