Category: SQL Server

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

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

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