Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

SQL Server Instead Of Insert Trigger Example

SQL Server Instead Of Insert Trigger Example

In this tutorial, I am going to explain you INSTEAD OF INSERT Trigger in SQL-Server with example. In the previous tutorials, I had explained introduction to triggers in SQL-Server. Please go through the links provided below.

Related Links

There are two main types of DML trigger.1. After Triggers (also called as FOR trigger)2. Instead of Triggers

After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after SQL Server completes the execution of the action successfully that fired it. Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.

Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table. Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.

Instead of Insert Trigger Example

When we try to insert a record into a table then instead of insert trigger is fired if trigger is associated with that table. Insert of trigger is mostly used with Views in SQL, but in this tutorial we are going to implement instead of trigger with single table. In article, we will use instead of trigger with Views. Now let's implement the trigger.

Create a table Employee with 3 columns EmployeeId, Name and Age. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

SQL-Server Date and Time Functions

SQL-Server Date and Time Functions

In this tutorial, I am going to explain you SQL-Server Date and Time functions with example. Below is the list of most commonly used Date and Time function.

  • GETDATE
  • SYSDATETIME
  • CURRENT_TIMESTAMP
  • GETUTCDATE
  • SYSDATETIMEOFFSET
  • SYSUTCDATETIME
  • ISDATE
  • DAY
  • MONTH
  • YEAR
  • DATENAME
  • DATEPART
  • DATEADD
  • DATEDIFF

Below is explanation for each function.

GETDATE Function

Syntax: GETDATE( ) Description: It is used to return the current database system timestamp as a datetime value without the database time zone offset.This value is derived from the operating system of the computer on which the instance of SQL Server is running. Example:

SQL-Server built-in String function Part 2

SQL-Server built-in String function Part 2

In this tutorial, I am going to explain you SQL-Server built-in String functions with example. Below is the list of most commonly used SQL-Server System defined String function.

We will discuss in this articleAlready discussed in previous article
  • CHARINDEX
  • SUBSTRING
  • REPLICATE
  • SPACE
  • PATINDEX
  • REPLACE
  • STUFF
  • ASCII
  • CHAR
  • UPPER
  • LOWER
  • LTRIM
  • RTRIM
  • REVERSE
  • LEN
  • LEFT
  • RIGHT
Click here to read more

Below is explanation for each function.

CHARINDEX Function

Syntax: CHARINDEX ( expressionToFind , expressionToSearch, [ Start_location ] ) Description: It is used to find index position of specified expression in a Given Expression. Start_location parameter is optional. Searching starts from first position if Start_location is not supplied. Returns Zero if specified expression is not found. Example:

SQL-Server built-in String function

SQL-Server built-in String function

In this tutorial, I am going to explain you SQL-Server built-in String functions with example. Below is the list of most commonly used SQL-Server System defined String function.

We will discuss in this articleWe will discuss in next article
  • ASCII
  • CHAR
  • UPPER
  • LOWER
  • LTRIM
  • RTRIM
  • REVERSE
  • LEN
  • LEFT
  • RIGHT
  • CHARINDEX
  • SUBSTRING
  • REPLICATE
  • SPACE
  • PATINDEX
  • REPLACE
  • STUFF
Click here to read more

Below is explanation for each function.

ASCII Function

Syntax: ASCII( Character_Expression ) Description: It is used to return ASCII code of the supplied character expression. Input expression can be char or varchar. It returns ASCII code of first character from left hand side. It does opposite of CHAR function. Example:

SELECT ASCII('a')  --97
SELECT ASCII('ab') --97
SELECT ASCII('A')  --65
SELECT ASCII('0')  --48
SELECT ASCII('1')  --49
SELECT ASCII('aA') --97 It returns ASCII code for first character only
SELECT ASCII(' ')  --32 (single space)

CHAR Function

Syntax: ASCII( Integer_Expression ) Description: It is used to convert an int ASCII code to a character. Input should be between 0 and 255. It does opposite of ASCII function. Example:

SELECT CHAR(97)  --a
SELECT CHAR(65)  --A
SELECT CHAR(255) --ÿ

--To print all characters whose acsii code is between 0 to 255
DECLARE @Number INT
SET @Number = 1
WHILE(@Number <= 255)
BEGIN
 PRINT CHAR(@Number)
 SET @Number = @Number + 1
END

UPPER Function

Syntax: UPPER ( character_expression ) Description: It is used to convert character expression into upper case character. Example:

SELECT UPPER('Welcome to ASPArticles') --WELCOME TO ASPARTICLES
SELECT UPPER('AbCd') --ABCD

LOWER Function

Syntax: LOWER ( character_expression ) Description: It is used to convert character expression into lower case character. Example:

SELECT LOWER('Welcome to ASPArticles') --welcome to asparticles
SELECT LOWER('AbCd') --abcd

LTRIM Function

Syntax: LTRIM ( character_expression ) Description: It is used to remove spaces from left side of character expression. Example:

SELECT LTRIM(' welcome') --removes spaces from left

RTRIM Function

Syntax: RTRIM ( character_expression ) Description: It is used to remove spaces from right side of character expression. Example:

SELECT RTRIM('welcome ') --removes spaces from right

REVERSE Function

Syntax: REVERSE ( string_expression ) Description: It is used to reverse the order of supplied string. Example:

SELECT REVERSE('ASPArticles.com') --moc.selcitrAPSA
SELECT REVERSE(12345) --54321
--here implicit conversion happens from int to varchar datatype and then reverses

LEN Function

Syntax: REVERSE ( string_expression ) Description: It is used to count total characters in the supplied string expression. It excludes the spaces at the end of the expression (right hand side). Example:

SQL Server After Delete Trigger with example

SQL Server After Delete Trigger with example

In this article, I am going to explain you AFTER Delete Trigger in SQL-Server with example. In the previous tutorial, I have explained introduction to triggers in SQL-Server. Please go through the links provided below.

Related Links

AFTER Delete Trigger (for delete) with Example

When we try to delete records from a table then delete trigger is fired if trigger is associated with that table. After Delete trigger is also called as For Delete trigger. Now, let's implement after delete trigger.

Create a table Employee with four columns EmployeeId, Name, Age and gender. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

Introduction to Triggers in SQL Server

Introduction to Triggers in SQL Server

In this article, I am going to explain you triggers in SQL-Server with examples. I have summarize all types of triggers as shown below. Links are provided for each triggers.

What is Trigger in SQL-Server

A trigger is a special kind of stored procedure that invokes or gets executed in response to certain action on table like insert, update or delete. You can not explicitly invoke triggers.

Types of Trigger

Below is types of triggers.
1. DML Triggers (Data Manipulation Language Triggers)
2. DDL Triggers (Data Definition Language Triggers)
3. CLR Triggers
4. Logon Triggers

DML Triggers

DML triggers are fired automatically in response to DML events that is INSERT, UPDATE and DELETE.
Below is types of DML triggers.
1. After Triggers (also called as FOR trigger)
2. Instead of Triggers

1. After Trigger (using FOR/AFTER CLAUSE)

This trigger fires after SQL Server completes the execution of the action successfully that fired it.
Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire only after the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will not fire the After Trigger.
AFTER Insert (for insert) → Click here to read more.
AFTER Update (for update) → Click here to read more
AFTER Delete (for delete) → Click here to read more

2. Instead of Trigger (using INSTEAD OF CLAUSE)

This trigger fires before SQL Server starts the execution of the action that fired it. This is much more different from the AFTER trigger, which fires after the action that caused it to fire. We can have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delete to the table.
Example: If you insert record/row in a table then the trigger associated with the insert event on this table will fire before the row passes all the checks, such as primary key, rules, and constraints. If the record/row insertion fails, SQL Server will fire the Instead of Trigger.
Instead Of Insert → Click here to read more.
Instead Of Update
Instead Of Delete

DISABLING and ENABLING Trigger

We can disable and enable trigger using SQL Server Management Studio or using SQL below query.
1. Using Management Studio
Expand your database → go to Tables and expand it → Go to your respective table and expand it → go to Triggers → Right click on trigger name and now you can modify, disable and delete ..etc.
2. SQL Query

--To disable
DISABLE TRIGGER tr_Employee_AfterUpdate on Employee
--To enable
ENABLE TRIGGER tr_Employee_AfterUpdate on Employee

--Before enabling/disabling you can check if exists
IF OBJECT_ID ('tr_Employee_AfterUpdate', 'TR') IS NOT NULL
begin
   DISABLE TRIGGER tr_Employee_AfterUpdate on Employee
   print 'Trigger disabled'
end
else
begin
   print 'No trigger found'
end

DROPPING Trigger

We can drop or delete triggers using SQL Server Management Studio or using SQL below query.
1. Using Management Studio
Already explained.
2. SQL Query

--To delete or drop
DROP TRIGGER tr_Employee_AfterUpdate

--Before deleting you can check if exists
IF OBJECT_ID ('tr_Employee_AfterUpdate', 'TR') IS NOT NULL
begin
   DROP TRIGGER tr_Employee_AfterUpdate
   print 'Trigger deleted'
end
else
begin
   print 'No trigger found'
end

List All triggers Associated with Table

SELECT 
 t.name AS TableName,
 tr.name AS TriggerName  
FROM sys.triggers tr
INNER JOIN sys.tables t ON t.object_id = tr.parent_id
WHERE 
t.name in ('Table_Name');

List All triggers Associated with database

SELECT OBJECT_NAME(parent_id) as Parent_Object_Name, *
FROM sys.triggers

SELECT 
     sysobjects.name AS trigger_name ,
    USER_NAME(sysobjects.uid) AS trigger_owner, 
    s.name AS table_schema ,
    OBJECT_NAME(parent_obj) AS table_name ,
    OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS 'Update Trigger',
    OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS 'Delete Trigger', 
    OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS 'Insert Trigger',
    OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS  'After',
    OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger')AS 'InsteadOf',
    OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS 'Disabled' 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 

 SELECT
    [so].[name] AS [trigger_name],
    USER_NAME([so].[uid]) AS [trigger_owner],
    USER_NAME([so2].[uid]) AS [table_schema],
    OBJECT_NAME([so].[parent_obj]) AS [table_name],
    OBJECTPROPERTY( [so].[id], 'ExecIsUpdateTrigger') AS 'Update Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsDeleteTrigger') AS 'Delete Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsInsertTrigger') AS 'Insert Trigger',
    OBJECTPROPERTY( [so].[id], 'ExecIsAfterTrigger') AS 'After',
    OBJECTPROPERTY( [so].[id], 'ExecIsInsteadOfTrigger') AS 'InsteadOf',
    OBJECTPROPERTY([so].[id], 'ExecIsTriggerDisabled') AS 'Disabled' 
FROM sysobjects AS [so]
INNER JOIN sysobjects AS so2 ON so.parent_obj = so2.Id
WHERE [so].[type] = 'TR'

After Update Trigger in SQL Server with example

After Update Trigger in SQL Server with example

In this article, I am going to explain you AFTER UPDATE Trigger in SQL-Server with example. In the previous tutorial, I have explained introduction to triggers in SQL-Server.

Related Links

AFTER Update Trigger (for update) with Example

First of all, create a table Employee with four columns EmployeeId, Name, Age and gender. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

CREATE TABLE Employee
(
EmployeeId INT PRIMARY KEY IDENTITY,
Name VARCHAR(20),
Age INT,
Gender VARCHAR(10)
)
GO
INSERT INTO Employee VALUES('Mr. A',50,'Male')
INSERT INTO Employee VALUES('Mr. B',55,'Male')
INSERT INTO Employee VALUES('Mr. C',60,'Male')
INSERT INTO Employee VALUES('Mr. D',65,'Male')
INSERT INTO Employee VALUES('Mr. E',70,'Male')
GO

Now, execute the select query to check inserted records.

SELECT * FROM Employee
EmployeeId Name Age Gender
1         Mr. A 50 Male
2         Mr. B 55 Male
3         Mr. C 60 Male
4         Mr. D 65 Male
5         Mr. E 70 Male
 

Now, we will create a Audit Table for Employee table. When somebody update record of Employee table, then we will insert same / updated record in audit table by using after update trigger. Below is the script to create Employee_Audit table.

CREATE TABLE Employee_Audit
(
Id INT IDENTITY PRIMARY KEY,
EmployeeId INT,
Name VARCHAR(20),
Age INT,
Gender VARCHAR(10),
UpdatedDate datetime
)

Creating AFTER UPDATE Trigger

Now, we will create after update trigger. Below is the script to create after update trigger.

CREATE TRIGGER tr_Employee_AfterUpdate
 
ON Employee    -- table name 
 
after UPDATE   --(you can also use 'for update')
 
AS
BEGIN
     DECLARE @EmployeeId INT,
             @Name VARCHAR(20),
             @Age INT,
             @Gender VARCHAR(20)
 
     SELECT @EmployeeId=i.EmployeeId,
            @Name=i.Name,
            @Age=i.Age,
            @Gender=i.Gender 
            FROM inserted AS i
 
       INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE())  
END

When you update record of Employee table then SQL-Server creates a magic table named as 'inserted' and same copy of record will we inserted into this magic table. Inserted table is accessible inside the context of trigger. You can not access inserted table outside of triggers.

Executing Update Trigger

As we can not execute triggers explicitly, to execute trigger, we need to update an employee as shown below. Here update Name column whose employeeid is 1.

UPDATE Employee SET Name ='Mr. Z' WHERE employeeid=1

As you can see below a record will be updated for Employee table and a new will be inserted for Employee_Audit table.

SELECT * FROM Employee
SELECT * FROM Employee_Audit
EmployeeId Name Age Gender
1         Mr. Z 50 Male
2         Mr. B 55 Male
3         Mr. C 60 Male
4         Mr. D 65 Male
5         Mr. E 70 Male
 
Id EmployeeId Name Age Gender UpdatedDate
1 1         Mr. Z 50 Male 2017-09-10 20:21:45.030

As you can see from above trigger, a record will be inserted in audit table whether a single column or multiple column or no column in updated. Here employee with id is 10 is not present in Employee table. Execute below script to update with employee whose id 10. Here NULL be inserted in Employee_Audit table irrespective of column is updated or not.

UPDATE Employee SET age =100 WHERE employeeid=10
GO
SELECT * FROM Employee_Audit
--Result:
Id EmployeeId Name Age  Gender UpdatedDate
1 1         Mr. Z 50  Male 2017-09-10 20:21:45.030
2 NULL         NULL NULL     NULL 2017-09-10 20:32:31.987

Now to stop above situation from happening we can count no of record for INSERTED table.

ALTER TRIGGER tr_Employee_AfterUpdate
ON Employee    -- table name 
after UPDATE   --(you can also use 'for update')
AS
BEGIN
 DECLARE @EmployeeId INT,
         @Name VARCHAR(20),
         @Age INT,
         @Gender VARCHAR(20)
 
 SELECT @EmployeeId=i.EmployeeId,
        @Name=i.Name,
        @Age=i.Age,
        @Gender=i.Gender 
        FROM inserted AS i
 
 
 DECLARE @inscnt INT; -- INSERTED row count
 SELECT @inscnt = COUNT(*) FROM INSERTED;
 IF @inscnt > 0
 BEGIN
 INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE()) 
 END
END

Now we can also check for specific column using UPDATE() function. If specific column is updated then only insert record into audit table.

ALTER TRIGGER tr_Employee_AfterUpdate
ON Employee    -- table name 
after UPDATE   --(you can also use 'for update')
AS
BEGIN
 DECLARE @EmployeeId INT,
         @Name VARCHAR(20),
         @Age INT,
         @Gender VARCHAR(20)
 
 SELECT @EmployeeId=i.EmployeeId,
        @Name=i.Name,
        @Age=i.Age,
        @Gender=i.Gender 
        FROM inserted AS i
 
 DECLARE @inscnt INT; -- INSERTED row count
 SELECT @inscnt = COUNT(*) FROM INSERTED;
 IF @inscnt > 0
 BEGIN
  IF UPDATE(Name) --check for updated column
  BEGIN
   INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE())
  END
 END
END

Now we can also check for multiple column is updated.

Using UPDATE() function

IF (UPDATE(Name) OR UPDATE(age))
BEGIN
 INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE()) 
END 
--OR
IF (UPDATE(Name) AND UPDATE(age))
BEGIN
 INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE()) 
END

Using COLUMNS_UPDATED() function

ALTER TRIGGER tr_Employee_AfterUpdate
ON Employee
after UPDATE
AS
BEGIN
 DECLARE @EmployeeId INT,
         @Name VARCHAR(20),
         @Age INT,
         @Gender VARCHAR(20)
 
 SELECT @EmployeeId=i.EmployeeId,
        @Name=i.Name,
        @Age=i.Age,
        @Gender=i.Gender 
        FROM inserted AS i
 
/*
Check whether columns 2, 3 or 4 have been column updated. If any or all  
columns 2, 3 or 4 have been changed, then insert into audit column.
Thebitmask is: 
power(2,(2-1)) + power(2,(3-1)) + power(2,(4-1)) = 14
 
Use IF (COLUMNS_UPDATED() & 14) > 0   -- for any or all (2, 3 or 4 columns is updated)
 
Use IF (COLUMNS_UPDATED() & 14) = 14 -- for all column (2, 3, and 4 columns are updated)
 
*/
 
 DECLARE @inscnt INT; -- INSERTED row count
 SELECT @inscnt = COUNT(*) FROM INSERTED;
 IF @inscnt > 0
 BEGIN
   IF (COLUMNS_UPDATED() & 14) > 0   --check if any/all  column is updated
  BEGIN
   INSERT INTO Employee_Audit VALUES(@EmployeeId,@Name,@Age,@Gender,GETDATE())
  END
 
  /*
  IF (COLUMNS_UPDATED() & 14) = 14   --check if all columns are updated
  begin
   insert into Employee_Audit values(@EmployeeId,@Name,@Age,@Gender,GETDATE())
  end
  */
 
 END
 
END

After Insert Trigger in SQL Server with example

In this article, I am going to explain you AFTER Insert Trigger in SQL-Server with example. In the previous tutorial, I have explained introduction to triggers in SQL-Server.

Related Links

AFTER Insert Trigger (for insert) with Example

We will be discussing only after insert trigger in this demo. First of all, create a table Employee with three columns EmployeeId, Name and Age. EmployeeId will be auto generated. Below is the code to create Employee table and insert some dummy records.

create table Employee
(
EmployeeId int primary key identity,
Name varchar(20),
Age int
)
go
insert into Employee values('Mr. A',50)
insert into Employee values('Mr. B',55)
insert into Employee values('Mr. C',60)
insert into Employee values('Mr. D',65)
insert into Employee values('Mr. E',70)
go
select * from Employee
Creating table for triggers

Now, we will create a Audit Table for Employee table. When somebody insert record into Employee table, then we will insert same record in audit table by using after trigger. Below is the script to create Employee_Audit table.

create table Employee_Audit
(
Id int identity primary key,
EmployeeId int,
Name varchar(20),
Age int,
InsertedDate datetime
)

Creating AFTER INSERT Trigger

Now, we will create a trigger. Below is the script to create after insert trigger.

create trigger tr_Employee_AfterInsert
on Employee
after insert   --(you can also use 'for insert')

as
begin
     declare @EmployeeId int,
             @Name varchar(20),
             @Age int
             
     select @EmployeeId=i.EmployeeId,@Name=Name,@Age=Age from inserted as i
    
     insert into Employee_Audit values(@EmployeeId,@Name,@Age,GETDATE())
          
end

When you insert a record into Employee table then SQL-Server creates a magic table named as 'inserted' and same copy of record will we inserted into this magic table. Inserted table is accessible inside the context of trigger. You can not access inserted table outside of triggers.

Executing AFTER INSERT Trigger

As we can not execute triggers explicitly, so we will insert a new record Employee table. When you insert new record into Employee table, after insert trigger will be fired and a new record will be inserted into Employee_Audit table as shown below. Initially we are having only 5 record in Employee table as shown above.

Before executing triggers

Now we will insert a new record in Employee table.

insert into Employee values('Mr. G',75)

As you can see below a new record will be inserted into both tables Employee and Employee_Audit.

After executing triggers

Coalesce function in SQL-Server with example

Coalesce function in SQL-Server with example

In this article, I am going to explain you Coalesce function in SQL-Server with example. Coalesce function returns first NON Null expression in given expression. It will return null when all expressions evaluate to null. Coalesce function takes 'n' number of arguments and arguments can be of any data type.

Syntax

Below is the syntax for Coalesce function.

coalesce( expression1, expression2, ..., expression_n)

Example 1

Let's understand coalesce function with examples. Below is coalesce function with select query.

select coalesce(null, 'Hello')  
select coalesce('Hello', null, null)  
select coalesce(null, null, 'Hello')  
select coalesce('ASPArticles','Hello') 

OUTPUT:
Hello
Hello
Hello
ASPArticles
Here we can see in above all four select statement, we get first non null expressions.

At least one of the arguments to COALESCE must be a typed NULL

You will get above error, if you pass null as value in all the input arguments. Null are not typed, that is why we get this error.

select coalesce(null,null)

OUTPUT:
Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be a typed NULL.

Let's try with null value as typed.

declare @Name varchar(10)
select coalesce(null, @Name)

declare @Name2 varchar(10)=null
select coalesce(null, @Name2)

OUTPUT:
NULL
NULL
Here, we don't get any error because one of argument (second argument) is typed.

Conversion failed when converting the varchar value to data type int

We will get above output when we supply two different data types.

select coalesce(null, 'Hello',1)

OUTPUT:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Hello' to data type int..

Example 2

Consider the below temporary table Employee where we have 4 columns. Some of columns contains null values and we want to find out first non null values in given FirstName, MiddleName and LastName.

create table #Employee
(
Id int,
FirstName varchar(10),
MiddleName varchar(10),
LastName varchar(10),
)
insert into #Employee values(1,'Rahul',null,null)
insert into #Employee values(2,null,'Ricky',null)
insert into #Employee values(3,null,null,'Sehwag')
insert into #Employee values(4,'Virat','Kohli',null)
insert into #Employee values(5,'Sachin','Ramesh','Tendulkar')

select * from #Employee

1 Rahul NULL NULL
2 NULL Ricky NULL
3 NULL NULL Sehwag
4 Virat Kohli NULL
5 Sachin Ramesh Tendulkar

Now, we will use coalesce function to find out first non null expression in FirstName, MiddleName and LastName in Employee table.

select id, coalesce(FirstName,MiddleName,LastName) as 'Name' from #Employee

OUTPUT:
1 Rahul
2 Ricky
3 Sehwag
4 Virat
5 Sachin

Now, insert one more record with null values in all the name columns.

insert into #Employee values(6,null,null,null)
select * from #Employee
OUTPUT:
1 Rahul NULL NULL
2 NULL Ricky NULL
3 NULL NULL Sehwag
4 Virat Kohli NULL
5 Sachin Ramesh Tendulkar
6 NULL NULL NULL

Here 6th record contains null value in all the columns. Now will use coalesce function.

select id, coalesce(FirstName,MiddleName,LastName) as 'Name' from #Employee
OUTPUT:
1 Rahul
2 Ricky
3 Sehwag
4 Virat
5 Sachin
6 NULL

Here in above output we get null in 6th row because all column contains null for the 6th record. We can use 4th input parameter as 'No Name Provided' in coalesce function as shown below.

select id, coalesce(FirstName,MiddleName,LastName,'No Name Provided') 
       as 'Name' from #Employee
       
OUTPUT:
1 Rahul
2 Ricky
3 Sehwag
4 Virat
5 Sachin
6 No Name Provided

Use Coalesce function in place of Case Condition

Here, we can use coalesce function instead of Case condition as shown below.

select id, case when FirstName is not null then FirstName
                when MiddleName is not null then MiddleName
                when LastName is not null then LastName
                else 'No Name Provided'
           end as 'Name'
          ,coalesce(FirstName,MiddleName,LastName,'No Name Provided') 
           as 'Name'
from #Employee

OUTPUT:
id Name   Name
1 Rahul   Rahul
2 Ricky   Ricky
3 Sehwag   Sehwag
4 Virat   Virat
5 Sachin   Sachin
6 No Name Provided No Name Provided

Update existing record and insert new record into table based on another table using MERGE in SQL-Server

In this article, I am going to explain you how to update a record in a table based on another table using SQL-Server MERGE statement. If record is already present in destination table then we will update the record into destination with source table based on common key column and if record is not present then we will insert new record into destination table.

Consider the below image, here we have two table "tblEmployee" and "tblTemp", we need to update Name column and insert two new records in tblEmployee from tblTemp using MERGE.

tblEmployee <--- tblTemptblEmployee
Insert and update record using MERGE in sql server Using MERGE in sql server

Below is the script to create two table and insert some dummy records.

CREATE TABLE tblEmployee
(
Id INT,
Name VARCHAR(50)
)
CREATE TABLE tblTemp
(
Id INT,
Name VARCHAR(50)
)
go
INSERT INTO tblEmployee VALUES(1,'Employee 1')
INSERT INTO tblEmployee VALUES(2,'Employee 2')
INSERT INTO tblEmployee VALUES(3,'Employee 3')

INSERT INTO tblTemp VALUES(3,'Updated Employee')
INSERT INTO tblTemp VALUES(4,'Employee 4')
INSERT INTO tblTemp VALUES(5,'Employee 5')

Using WHEN MATCHED clause

MERGE INTO tblEmployee e
USING tblTemp t on e.Id=t.Id
WHEN MATCHED THEN
 UPDATE SET e.Name=t.name;
 
SELECT * FROM tblEmployee

Using WHEN MATCHED AND WHEN NOT MATCHED clause

MERGE INTO tblEmployee e
USING tblTemp t on e.Id=t.Id
WHEN MATCHED THEN
 UPDATE SET e.Name=t.name 
WHEN NOT MATCHED THEN
 INSERT (Id,Name) VALUES(t.Id,t.Name);
 
SELECT * FROM tblEmployee

In later article, I will discuss delete clause also.

LEN() function in SQL Server with example

LEN() function in SQL Server with example

In this article, I am going to explain you the LEN() function in SQL-Server with syntax and examples. The LEN() function in SQL-Server returns the number of characters in a given string expression. LEN() function does not include trailing blank spaces while calculating length.

Syntax

Below is the syntax for LEN() function in SQL-Server.

LEN ( string_expression )

Input Parameter

Input parameter is a string_expression. It can be a constant, variable or column of character or binary data.

Return Type

It returns bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types otherwise returns int.

Examples

select LEN('SQLServer') as 'CharCount'
CharCount : 9

select LEN('SQLServer  ') as 'CharCount'   --Here we have 2 blank spaces from trailing
CharCount : 9                              -- It does not count trailing spaces

select LEN('  SQLServer    ') as 'CharCount' --Here we have 2 blank spaces from Right and 4 from trailing
CharCount : 11                               -- It does not count trailing spaces
                                         
select LEN(NULL) as 'CharCount'      --When parameter is null, it returns null
CharCount : NULL

select LEN(' ') as 'CharCount'
CharCount : 0

select LEN('   ') as 'CharCount'
CharCount : 0

select Name, LEN(Name) as [Length] from tblEmployee
OUTPUT:
Name    Length
Rahul    5
Ravi     4
John     4

select Name, LEN(Name) as [Length] from tblEmployee where LEN(Name)<5
OUTPUT:
Name    Length
Ravi     4
John     4

select Name, LEN(Name) as [Length] from tblEmployee where LEN(Name)>5 and LEN(Name)<10
OUTPUT:
Name    Length
Yogesh  6

Data Type Support

It support char, varchar, nchar and nvarchar. It does not support text, ntext and image data types. Let's check with an examples. Here, we have a table variable and it has Name column of text data type.

declare @tblEmployee table(
ID int,
Name text
)

insert into @tblEmployee values(101,'Rahul')
insert into @tblEmployee values(102,'Ravi')
 
select LEN(Name) as 'Count' from  @tblEmployee

Msg 8116, Level 16, State 1, Line 8
Argument data type text is invalid for argument 1 of len function.

When input is nvarchar

declare @Name nvarchar(20) 
set @Name= 'Rahul'
select len(@Name) as 'CharCount'
CharCount : 5

declare @Name nvarchar(20) 
set @Name= 'Rahul   '       -- with trailing spaces
select len(@Name) as 'CharCount'
CharCount : 5

select LEN(N'Rahul') as 'CharCount'
CharCount : 5

How to use Microsoft Enterprise Library in ASP.Net application

In this article, I will explain you how to how to use Microsoft Enterprise Library to connect to SQL-Server database and perform some DML operations like select, insert, update and delete with simple examples and also using stored procedure.

The Microsoft Enterprise Library is a collection of reusable software components (application blocks) designed to assist software developers with common enterprise development cross-cutting concerns (such as logging, validation, data access, exception handling, and many others). There are various versions available.

  1. Enterprise Library 6.0 (April 2013) - Current
  2. Enterprise Library 5.0 Windows Azure Integration Pack (Dec 2011) - Current
  3. Enterprise Library 5.0 Optional Update 1 (May 2011) - Current
  4. Enterprise Library 5.0 Silverlight Integration Pack (May 2011) - Active
  5. Enterprise Library 5.0 (April 2010) - Active
  6. Enterprise Library 4.1 (October 2008) - Deprecated
  7. Enterprise Library 4.0 (May 2008) - Deprecated
  8. Enterprise Library 3.1 (May 2007) - Deprecated
  9. Enterprise Library 3.0 (April 2007) - Deprecated
  10. Enterprise Library 2.0 (January 2006) - Active/Retired
  11. Enterprise Library 1.1 (June 2005) - Deprecated
  12. Enterprise Library 1.0 (January 2005) - Deprecated

Related Articles

  1. Calling stored procedure with OUTPUT parameter using C# (C-Sharp) in ASP.Net
  2. How to pass table-valued parameter to stored procedure using C# with ado.net

Creating application

In this article, I will explain you with single-tier application. Later on I will explain you how to use Microsoft Employee Library's all the blocks with data access layer. First of all, create a ASP.NET Empty Web Application.

Installing Enterprise Library Using Package Manager Console

Next step is to install Microsoft Enterprise Library. You can download Microsoft Enterprise Library's application blocks from Microsoft's website or can install using Package Manager Console.
To install using Package Manager Console, go to Tools-> NuGet Package Manager-> Package Manager Console, after that type "Install-Package EnterpriseLibrary.Data -Version 5.0.505" and press enter as shown below.

Installing microsoft enterprise library using nuget

After successful installation, you can check in references folder there all the necessary dll files will be added.

Microsoft practices enterprise libray dll reference

Creating Table

We need to create a table. Below is the script to create sql table.

create table tblEmployee
(
Id int primary key identity,
Name varchar(50),
Gender varchar(10)
)
insert into tblEmployee values('Rahul','Male')
insert into tblEmployee values('John','Male')
insert into tblEmployee values('Mery','Female')
insert into tblEmployee values('Manish','Male')

Include Namespace

Add below namespaces.

using System.Configuration;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

Execute Select Query

Below is the code to execute select query with ExecuteDataSet command.

string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(con);
DbCommand dbCommand = db.GetSqlStringCommand("select * from tblEmployee");
dbCommand.CommandType = CommandType.Text;
DataSet ds = db.ExecuteDataSet(dbCommand);

Execute Insert Query

Below is the code to insert record with ExecuteNonQuery command.

string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(con);
DbCommand dbCommand = db.GetStoredProcCommand("insert into tblEmployee values(@Name,@Gender)");
dbCommand.CommandType = CommandType.Text;
 
db.AddInParameter(dbCommand, "@Name", DbType.String, "Mathew");
db.AddInParameter(dbCommand, "@Gender", DbType.String, "Male");
 
int Result = db.ExecuteNonQuery(dbCommand);

Execute Stored Procedure with OUTPUT Parameter

Next is to execute stored procedure, which will return number of Male Employee or Female Employee based on Gender supplied from INPUT parameter. Below is the script to create stored procedure.

create procedure usp_EmployeeGender
@Gender varchar(10)=null,
@GenderCount int output
as
begin
 select @GenderCount=COUNT(*) from tblEmployee where Gender=@Gender
end

Below is the code to call stored procedure with output parameter.

string con = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
Database db = new Microsoft.Practices.EnterpriseLibrary.Data.Sql.SqlDatabase(con);
 
DbCommand dbCommand = db.GetSqlStringCommand("usp_EmployeeGender");
dbCommand.CommandType = CommandType.StoredProcedure;
 
db.AddInParameter(dbCommand, "@Gender", DbType.String, "Male");
db.AddOutParameter(dbCommand, "@GenderCount", DbType.Int32, 4);
 
db.ExecuteNonQuery(dbCommand);
 
int GenterCount = Convert.ToInt32(db.GetParameterValue(dbCommand, "@GenderCount"));