asked
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][1]. 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][2]. 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. [1]:
http://msdn.microsoft.com/en-us/library/ms187956.aspx [2]:
http://technet.microsoft.com/en-us/library/ms161953(v=SQL.105).aspx
Grant: First of all, thanks so much for your clear reply. If I use the stored procedure model as you suggest, should the result be stored in a temporary table instead of a view, right? I'm guessing with this model each user would build its own temporary table, which can then be dropped once the user leaves the inquiry screen. And yes, I used the "WITH (NOLOCK)" in the example, but in real life we're getting rid of it. Thanks again!
No, no need to store it in a temporary table. Run the query within the stored procedure and you'll get a result set. Temporary storage isn't needed from what you've defined.
You were very helpful, Grant. I think I have a better idea about this than before! Thanks.
15 People are following this question.
