Recursive Queries a Replacement to connect by prior.

Recursive Queries are applied to retrieve data which is presented in a hierarchical format.
Here in the below example we can see how to use Common Table Expression(CTE) in Recursive Queries
CTEs can also contain references to themselves. This allows the developer to write complex queries simpler.
CTEs can also be used as Views

Here we go..!!!

Example for Recursive CTE,
DECLARE @Employees TABLE(EmployeeID INT,
LastName VARCHAR(100),
Master INT)
INSERT INTO @Employees VALUES (1,'A',null)
INSERT INTO @Employees VALUES (2,'B',1)
INSERT INTO @Employees VALUES (3,'C',1)
INSERT INTO @Employees VALUES (4,'D',2)
INSERT INTO @Employees VALUES (5,'E',2)
INSERT INTO @Employees VALUES (6,'F',3)
INSERT INTO @Employees VALUES (7,'G',6)
INSERT INTO @Employees VALUES (8,'H',6)
INSERT INTO @Employees VALUES (9,'I',8)
INSERT INTO @Employees VALUES (10,'J',9)

SELECT * FROM @Employees




;WITH Master
AS
(
--main Query
SELECT EmployeeID, LastName, Master
FROM @Employees
WHERE EmployeeID = 3
UNION ALL
--recursive part
SELECT e.employeeID,e.LastName, e.Master
FROM @Employees e INNER JOIN Master m
ON e.Master = m.employeeID
)
SELECT * FROM Master

NULLIF,ISNULL,COALESCE

NULLIF,ISNULL,COALESCE

NULLIF (expr1, expr2)

Returns null value when both expression are equal; if not it returns the first expression

ISNULL (expr_check, expr_replace)

Returns a value that indicates whether the value of the specified column is null.


COALESCE (expr1...exprn)

Returns first non-null expression among its arguments
If all arguments are NULL, COALESCE returns NULL.
Rows in a table can be converted into columns using COALESCE.
Please see the below example.

Here we GO!!!!

DECLARE @table TABLE (id INT IDENTITY(1,1),FirstName VARCHAR(50),
LastName VARCHAR(50),
Current_income NUMERIC(19,2),
Previous_income NUMERIC(19,2))

INSERT INTO @table VALUES('Karthik','M',null,null)
INSERT INTO @table VALUES('Suman','PV',null,20.00)
INSERT INTO @table VALUES('First','Last',30.25,null)

Example for NULLIF:
SELECT id,FirstName+' '+LastName AS FullName,
NULLIF(current_income,Previous_income) AS Income
FROM @table



Example for ISNULL:
SELECT id,FirstName+' '+LastName AS FullName,
ISNULL(current_income,Previous_income) AS Income
FROM @table


Example for COALESCE:
SELECT id,FirstName+' '+LastName AS FullName,
COALESCE(current_income,Previous_income) AS Income
FROM @table


Example for ISNULL Combined with COALESCE:
SELECT id,FirstName+' '+LastName AS FullName,
ISNULL(coalesce(current_income,Previous_income),0.00) AS Income
FROM @table


Example to convert rows into columns using COALESCE:
DECLARE @concat varchar(300)
SELECT @concat = COALESCE(@concat+', ','')+FirstName FROM @table
WHERE current_income IS NULL
SELECT @concat as Names

ON DELETE CASCADE

ON DELETE CASCADE
ON UPDATE CASCADE

Cascading allows one to delete or update the child table once the master table gets affected.

SQL Server has CASCADE options for delete and update.

• [ ON DELETE { CASCADE | NO ACTION } ]
• [ ON UPDATE { CASCADE | NO ACTION } ]

• ON DELETE CASCADE
Specifies that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing those foreign keys are also deleted. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the rows deleted from those tables.
• ON UPDATE CASCADE
Specifies that if an attempt is made to update a key value in a row, where the key value is referenced by foreign keys in existing rows in other tables, all of the foreign key values are also updated to the new value specified for the key. If cascading referential actions have also been defined on the target tables, the specified cascading actions are also taken for the key values updated in those tables

Note:
CASCADE cannot be applied to a timestamp column.

Here is the sample for Cascading

CREATE TABLE staff
(staff_id INT NOT NULL CONSTRAINT p_staff_id PRIMARY KEY,
staff_name VARCHAR(50) NOT NULL,
staff_role VARCHAR(10) NULL )

CREATE TABLE job
(job_id INT NOT NULL CONSTRAINT p_job_id PRIMARY KEY,
job_name VARCHAR(15) NOT NULL )

CREATE TABLE staff_job
(staff_id INT NOT NULL,
job_id INT NOT NULL,
comments VARCHAR(200) NULL,
created_date DATETIME NULL,
CONSTRAINT p_staff_job PRIMARY KEY(staff_id, job_id),
CONSTRAINT f_staff_job1 FOREIGN KEY(staff_id) REFERENCES staff(staff_id)ON DELETE CASCADE,
CONSTRAINT f_staff_job2 FOREIGN KEY(job_id) REFERENCES job(job_id)ON UPDATE CASCADE
)


insert into staff values(1,'Staff1' ,'DBA')
insert into staff values(2,'Staff2' ,'DBA')
insert into staff values(3,'staff3','UI')


insert into job values(1,'Backup' )
insert into job values(2,'Restore' )

insert into staff_job values(1,1,'created a backup',getdate())
insert into staff_job values(2,2,'Restored the Backup',getdate())
insert into staff_job values(3,2,'workdone',getdate())

select * from staff
select * from job
select * from staff_job




here we are going to update and delete the master record.

update job set job_id = 3 where job_name = 'Restore'

delete from staff where staff_id = 1
GO

select * from staff
select * from job
select * from staff_job





GO
drop table staff_job
drop table job
drop table staff
GO