Create better Stored Procedures using Common Table Expressions

SQL TUTORIAL - CTE - USING COMMON TABLE EXPRESSIONS - SQL COMMAND

If you are new to creating Stored Procedure please view this video which shows you how to create and use stored procedures :

DESCRIPTION :
A CTE (Common Table Expression) is like a dervied table.

REASON TO USE :
You would otherwise need to create a derived table\view.
One advantage of the CTE is that you do not have to define the table used to store the table.

EXAMPLE :

SIMPLE CTE EXAMPLE :
This example returns a table called backup_details with the name and backup_type fields.

WITH backup_details(name,backup_type) AS
(
SELECT name,backup_type from dbo.Backup_History WHERE Server='MYTESTSERVER'
)
SELECT * from backup_details where backup_type='Database'

MULTIPLE CTEs :
--Notice that the 2 CTE's are seperated by a command and the second CTE does not contain
a WITH clause

;WITH backup_details(name,backup_type,Server) AS
(
SELECT name,backup_type,Server from dbo.Backup_History
),

serverlist(servername) AS
(
SELECT servername FROM list_of_servers
)
SELECT * from backup_details
INNER JOIN serverlist servers

OTHER USES:
More advanced uses of CTEs are recursive ctes , these examples all the WITH clause several times
and are useful in such senarios a manager\employee tables that have a hierarchy.

OTHER SQL COMMANDS :
UPDATE
MERGE
SELECT
---------------------------------------------

Want to learn Qlik Sense ?
'Qlik Sense for Beginners' on Amazon.