Getting to grips with Dynamic SQL: Debugging

Something that many find difficult with Dynamic SQL is debugging it. When looking at a batch that creates and executes a dynamic statement it can be daunting to understand where exactly the error is happening, or even where the SQL that is generating the error is coming from, as it might be a value that was injected, rather than part of the literal strings. I’ve touched on this before, but to reiterative Formatting is Important. That doesn’t just mean with your statements that are creating the dynamic SQL, it means ensuring that the dynamic SQL you create is well formatted…

Continue reading

Getting to grips with Dynamic SQL: Working with datasets

Introduction Previously I covered how to validate the values that are being received by your dynamic SQL to help ensure that malicious code is even harder to be run. In this article I’m going to cover how you can use a dataset to create a dynamic statement, or dynamic statements. This will likely be as you want to perform a specific task against several objects in your database, or server, and instead of writing the same statement for each object you use a dynamic one to do the work. These are, in my opinion, the more valid reason for dynamic…

Continue reading

Getting to Grips with Dynamic SQL: Validating inputs

In the last article I touched on how you would build a dynamic query which is likely, in the end, accepting an input from a user to produce a dynamic statement. These work well but often, for added security, it’s best to also validate the input value prior to executing the statement. This either means that the dynamic SQL isn’t executed at all, or you can choose to return a custom error message. Validating Objects When using dynamic objects, then use you use the sys or INFORMATION_SCHEMA objects to check that the object does intend exist. Personally I prefer the…

Continue reading

Getting to Grips with Dynamic SQL: The beginning

Dynamic SQL is a tool that many of us may end up using in the future, or have in the past. Often, however, it’s use can frowned upon because it often can be a sign that what you have really is a design problem, or what is known as an “XY Problem”. Most of the questions I do see are an indication of this, such as “I need a Procedure that accepts a table name as a parameter for an INSERT.” These sorts of requirements are often clear examples, as this likely means you are using objects names to infer…

Continue reading