Code is an ever moving target. Version control and documentation only go so far, if they even exist. Sometimes all you have is the code in front of you. This is why I always start stored procedures with a header. Here’s a template as a starter: Name seems redundant as we have the proc name below […]
We have database roles for reading and writing data but interestingly there’s no role which provides permission to execute procedures. Most DBAs I’ve worked with – production or development – prefer to use stored procedures for data access rather than an ORM. A role to allow procedure execution would be very handy. So let’s fix […]
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 […]
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 […]
If writing code is the meat of our work then then packaging our stored procedures for deployment is the bread and butter to complete the sandwich. Deploying them is key so here we’ll be looking at options for how to effectively script those changes. The specific challenge we’re looking at here is how to change […]
We recently looked at the impact of rolling back transactions on statistics and I thought it would be worth following this up to look at some other objects to see how they behave when a rollback occurs. When rolling back transactions we expect any changes to be rolled back. This isn’t always the case with data, just […]
Andy Brownsword 