Reading Time: 2 minutesThe COALESCE system function offers a great way to check expressions for NULL. The syntax for COALESCE is very simple. It looks like this: COALESCE(<expr1>,<expr2>,<expr3>,<expr4>,….<exprN>) As you can see, all we do is outline a comma-separated list of expressions to the function. SQL Server will evaluate each expression, from left Continue Reading
Error Handling
SQL Server ISNULL: Explained with Examples
Reading Time: 3 minutesThe SQL Server ISNULL system function is one of the most useful functions you will use in your work as a data professional. The ISNULL function is meant to help you deal with NULLs in SQL Server. It’s meant to be used as an error handling tool of sorts. The Continue Reading
SQL Server WAITFOR DELAY: Explained
Reading Time: < 1 minuteThe WAITFOR DELAY system function is the best tool we have in Microsoft SQL Server to introduce a pause in our SQL code. The syntax is extremely simple. We just run the WAITFOR DELAY statement on its own, like this: WAITFOR DELAY ’00:00:10′ You can see how in single quotes, Continue Reading
How to Gather User ID Values in SQL Server: Just use this one helpful function.
Reading Time: < 1 minuteThere may be times where it might be helpful to gather the ID of the person or application that is interacting with the data or objects in your database. Maybe you are trying to audit changes to your data, or maybe you want to get a better idea of how Continue Reading
SQL Server TRY_CONVERT: Explained with Examples
Reading Time: 2 minutesThe SQL Server TRY_CONVERT system function is a great alternative to the standard CONVERT function. We use CONVERT and TRY_CONVERT to change an expression from one data type to another “on the fly“. This begs the question: What’s the difference? TRY_CONVERT will return NULL if the conversion fails. Here is Continue Reading
How to break a WHILE loop in SQL Server: Explained with Examples
Reading Time: 2 minutesThe task of breaking a WHILE loop in Microsoft SQL Server is very simple. We basically break a while loop when we want to terminate the loop early. That is, earlier than the loop would have ended if left to it’s natural progression. To break a WHILE loop early, just Continue Reading
SQL Server NULLIF: Explained for Beginners
Reading Time: 5 minutesThere are many ways we can compare the equivalency of two values in Microsoft SQL Server. We can use the regular equals ( = ) operator in an IF statement, for example, or we can use the handy CASE statement within the column list of a SELECT statement. But there Continue Reading
SQL Server CONVERT: A How-To Guide with Examples
Reading Time: 7 minutesThe SQL Server CONVERT function is an extremely handy tool to know when it comes to querying SQL Server databases. CONVERT is a system function we can use to very easily convert a value from one data type to another on-the-fly. In this very brief tutorial, you’ll learn everything you Continue Reading
SQL Server ROLLBACK: Everything you need to know
Reading Time: 9 minutesIn SQL Server, there are plenty of instances where you might want to undo a statement that was just ran. Maybe you wrote an UPDATE statement that modified too many rows, or maybe an INSERT statement wasn’t quite right. In these instances, it is great when the work is performed within a Continue Reading
Are you making this ROLLBACK mistake in your stored procedures?
Reading Time: 9 minutesIf you read my post about the 6 rules you should know about SQL Server transactions, you should know there is one rule that can be a bit tricky. This rule centers around the idea of doing a ROLLBACK within a stored procedure. The rule is this: When you leave Continue Reading