Recent Blog PostsIsItSQL 2.0 (Bill Graziano) SQL Server Desired State Configuration (Bill Graziano) Naming Linked Servers v2 (Bill Graziano) ClearTrace for Extended Events (Bill Graziano) Running GO as a Windows Service (Bill Graziano) Send SQL Server Extended Events to Logstash (ELK) (Bill Graziano) SQL Server Extended Events in Production (Bill Graziano) Is It SQL - 1.0.28 now with better AG backups (Bill Graziano) Get a Phone Alert When a Query Finishes (Bill Graziano) PASS Summit Speaker Idol 2017 (Most Valuable Yak (Rob Volk)) New Forum TopicsSelect a single row based on conditions in multiple rows (3h) I want Help Managing Big Data Sets in T SQL Efficiently (14h) SQL stored procedure to load the error and correct record based on some business rules (23h) Query is running too long (1d) Sql Query to check status change of an item (1d) Can I create differential backups tied to a specifc Full backup instead of the most recent? (7d) Using Dynamic SQL in Stored ProceduresDynamic SQL allows stored procedures to “write” or dynamically generate their SQL statements. The most common use case for dynamic SQL is stored procedures with optional parameters in the WHERE clause. These are typically called from reports or screens that have multiple, optional search criteria. This article describes how to write these types of stored procedures so they execute well and resist SQL injection attacks. 7 March 2011 - Bill Graziano Handling SQL Server ErrorsThis article covers the basics of TRY CATCH error handling in T-SQL introduced in SQL Server 2005. It includes the usage of common functions to return information about the error and using the TRY CATCH block in stored procedures and transactions. 5 April 2010 - Bill Graziano Using REPLACE in an UPDATE statementThis article covers using the REPLACE function to selectively replace text inside a string in SQL Server. The REPLACE function is easy to use and very handy with an UPDATE statment. 31 March 2010 - Bill Graziano Efficiently Reuse Gaps in an Identity ColumnThis article will demonstrate an efficient way to reuse gaps in an identity column. Please note that this is something you normally shouldn't be bothered about in a well-designed database or application. However, there are circumstances where you are forced to do this. 9 February 2010 - Peter Larsson Testing with Profiler Custom Events and Database SnapshotsWe've all had them. One of those stored procedures that is huge and contains complex business logic which may or may not be executed. These procedures make it an absolute nightmare when it comes to debugging problems because they're so complex and have so many logic offshoots that it's very easy to get lost when you're trying to determine the path that the procedure code took when it ran. Fortunately Profiler lets you define custom events that you can raise in your code and capture in a trace so you get a better window into the sub events occurring in your code. I found it very useful to use custom events and a database snapshot to debug some code recently and we'll explore both in this article. I find raising these events and running Profiler to be very useful for testing my stored procedures on my own as well as when my code is going through official testing and user acceptance. It's a simple approach and a great way to catch any performance problems or logic errors. 22 June 2009 - Mike Femenella Advanced SQL Server 2008 Extended Events with ExamplesIn the previous article we learned about SQL Server 2008 Extended Events terminology, components and took a look at a simple example. In this article we'll take a deeper look with some more complex examples. 25 May 2009 - Mladen Prajdić Introduction to SQL Server 2008 Extended EventsSQL Server 2008 Extended Events are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events. 19 May 2009 - Mladen Prajdić Monitoring SQL Server Agent with PowershellThis article introduces the reader to Powershell. The application that it demonstrates is one that monitors SQL Server Agent to make sure it is running. 24 March 2009 - Mike Femenella |
- Advertisement - |