Don’t pass over synonyms

I am finally back in the writing saddle again with a spree of tidbits for the month of April 2015. These articles will focus on learning a simple TSQL command or technique. Today, I want to talk about how synonyms can be used to preserve backward compatibility objects while allowing for schema redesign in existing databases. The CREATE SYNONYM and DROP SYNONYM key words were added to the TSQL language in the 2005 version. The main purpose of the construct is to provide a simple name to reference a database…

Is my string a number?

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One classic questions asked by new database developers is whether or not the user input is a number? I thought it was an excellent opportunity to review what tools are available given a…

Replacing Data – Part 2

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? In my last article, I created a inline table valued…

Replacing Data – Part 1

I have been recently devoting a-lot of time answering questions on STACKOVERFLOW. Many times, you are just helping and/or teaching younger SQL Server developers and/or administrators the tricks of the trade. Sometimes you come across a GEM, a question that you can blog about so that when they are asked again, you have a well constructed answer. One such question was recently asked. How do you replace all the digits in a credit card, expiration date and authorization code string? The Transaction SQL language does come with a REPLACE() string…

Error Handling – Part 4

I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program. Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next. The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program. Today, I want to talk about how to return a custom error message from the business logic layer to the application…

Error Handling – Part 3

I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program. Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next. The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program. Today, I want to talk about creating, altering and deleting custom error messages. Therefore, the sys.sp_addmessage, sys.sp_altermessage and sys.sp_dropmessage are key…

Error Handling – Part 2

I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program. Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next. The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program. Today, I want to talk about how to use the TRY/CATCH constructs to detect and handle errors. I consider this new…

Error Handling – Part 1

I am going to continue my articles on controlling the execution flow of statements in a Transaction SQL (T-SQL) program. Every program has to be deterministic on what step to execute next. Otherwise, the computer would be clueless on what to do next. The only exception to this rule is error handling. A runtime error may cause the program to branch to a error handler and exit the program. Today, I want to talk about how to use the @@ERROR system function to determine if an error occurred. This function…

Using Variables

I am going to talk about Transaction SQL variables. Any stored program that does complex business processing will use one of more of variables to craft an algorithm. During my college days, I had to take a couple physics classes. Newton’s second law states that force is equal to mass times acceleration. The comic strip on the left shows how variables in the fourth picture can be used as place holders in a complex equation. This concept is key for crafting very complex programs. The DECLARE keyword is used in…

Crafting Views with SSMS

Most of the articles that I presented so far for database development have been centered around the exact Microsoft T-SQL syntax to perform a certain operation. At the University of Rhode Island, I learnt how to program in Pascal before typing in my first ANSI SQL statement. In short, I will always be some type of developer in my heart. However, many people do not come from a computer science and math background. The SQL Server Management Studio (SSMS) has menus and dialog boxes to achieve many of the same…