When looking to migrate, consolidate or deprovision parts of a SQL solution it’s key to understand the dependencies on the objects inside. Identifying dependencies can be challenging and I wanted to demonstrate one way to approach this. We’ll start with some objects across a couple of databases: The simplest option to find references within a […]
Tag: Dynamic SQL
Dynamic SQL has many uses and one of these can help us fix Parameter Sniffing issues. Here we’ll look at how it can be used to generate multiple execution plans for the same query. Parameter sniffing is a common issue. Even for simple queries we can run into suboptimal plans being produced. There are multiple ways we […]
Constructing Dynamic SQL with Parameters
When building dynamic SQL, safety is crucial. As we saw last week, we have the QUOTENAME function which can help when referencing object names. Another aspect to consider is use of parameters. Integrating them incorrectly can leave us vulnerable to SQL injection attacks. Let’s take a look at how to handle them the wrong way, followed by the right […]
Securing Dynamic SQL with QUOTENAME
I’m a big fan of dynamic SQL in the right conditions. One key to crafting safe dynamic query of the use of the QUOTENAME function. The issue Using dynamic SQL can leave us vulnerable without proper safeguard. Let’s see an example of this: This is a cut down example of something I’ve seen previously in […]
This months’ invitation from Steve Jones asks us to write about producing SQL dynamically in some form or another. We can create and execute SQL dynamically within our procedures but what about dynamically creating it from other sources before passing it into SQL Server? The quick I have to admit that Steve’s mention of using Excel […]