Programming Examples

Are you a Programmer or Application Developer or a DBA? Take a cup of coffee, sit back and spend few minutes here :)

SQL Server

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

SQL Cursor Explained

Fig 3. Iterate and fetch the record data into variables

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.

Continue Reading →

SQL CLR Trigger Explained

SQL-CLR Trigger Code Example

 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.

Continue Reading →