Articles on SQL, TSQL and Management Studio. You can download Microsoft supplied sample database here: https://drive.google.com/file/d/0B8H2WLrOYbPxcy13RG9JNUpRRHM/edit?usp=sharing
In SQL Server, in some cases we need to replace a null value with more descriptive value. For example, while we select a record from a table, we can replace the null value from a column into a more meaningful value. The TSQL IsNull function can do the trick for us.
Let us say in a SQL Script, in Stored Procedure, or in a function, we want to process the data record-by-record. In this case, we can go for a SQL Cursor. Note, using Cursor is not advisable as it eats up memory, network bandwidth and performs lock while processing the records. So, before writing a cursor think twice and use it when no other options are available. With this overhead, we will get full control over the record processing. In this example, we will create a SQL cursor to fetch record from the Jobs table of the pubs database and list the records. In real world, after reading the record, there will be business logic that performs various other actions based on the fetched data.
In some case, we may need to pause the execution for certain period say for example 5 seconds. And in some situation, we may need to pause the execution till some time of the day elapses. In this example, we will see how to introduce execution delay in SQL Server T-SQL Script using the WaitFor clause.
The Loop and condition are useful when writing the SQL Scripts to achieve the desired goals. In this SQL Server example, we will learn:
1) If & Else Conditions
2) Case & When Structures
3) While loops
In the last article, we saw creating a SQL CLR Function. I would suggest reading that article first before reading this one. Like CLR function, one can write SQL Trigger also in the C-Sharp. In this article, we will learn to create the SQL CLR Trigger and execute that in SQL Server Management studio.
In this example, we are going to write a CLR Trigger that does not allow entering the Negative Bonus in the Emp table.