Duration: 17:42 min
Summary: In this video, we will discuss common table expressions and dynamic sql. A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. We will create an example on how to use a CTE. For dynamic SQL, we will look at the use of ‘exec’ and ‘sp_executesql’. When it comes to dynamic SQL, you need to be careful and avoid SQL injection attacks where users can put damaging statements in the input fields. If you are building a SQL statement that includes these fields, you will end up running those damaging statements (like dropping tables…) and losing valuable data. There are many articles on the internet regarding dynamic SQL and SQL injection attacks.