My workplace uses a large number of stored procedures and views in our data management and as a consequence, I have had to learn to use and even bu...
For further actions, you may consider blocking this person and/or reporting abuse
Great comparison! It's important to note that nesting views (referencing a view within a view) will almost certainly cause performance issues. I would also note that views are best used to obfuscate data from a source table when other data in that table is needed. Think of a report writer who needs access to employee name and title, but shouldn't have access to ssn or salary. You can create a view with only the columns you need and grant access to the view. In general, I wouldn't recommend using views simply for commonly joined tables. Developers should always write out all of their code or risk unintended changes to the view or nesting of views, IMHO. I would only recommend using views to obfuscate data or to present a simplified version of a query to a report writer who isn't privy to the schema of the database or particularly proficient in SQL.
This is not true. A stored procedure could have a flaw and be vulnerable to SQL injection. That's very easy to do.
This is assuming that the stored procedure takes a VARCHAR or NVARCHAR parameter(s) AND uses these params to build a dynamic query. I think @JaredKarney meant that when you are using a stored procedure you are not building a dynamic sql statement. It's when you use dynamic sql statements that you are vulnerable to sql injection.
I've updated the sections where I mentioned SQL Injection, as that is not something I am very familiar with. Thank you for the correction!
I rarely create views because when joining table you might have to join with table hints, or the view has more then you need. On top of this you can't use parameters.
You can create a table function that is like a SP but you call it like a table or view. And as a extra bonus you can use a cross/outer apply with a table function with a parameter and use a column from the table being connected to.
But if you have to create a SP and need to join the data you can always prefix a insert into infront of the exec command.
I would recommend when possible use with SCHEMABINDING to help with having a change brake your view or SP