In the sql most of the new developers don’t know how to use WITH in SQL query. Its simple and use full for writing big query in the stored procedure. WITH will provide a way of using sub query in the larger select query. It’s a kind of temp table in the sql query. One use of this feature is to break down complex query into similar parts. The below is a simple example for with in querying.
;with table1 as
select * from MYTable
select * from MyTable2
select * from MyTable99
select * from table1
inner join Table2.ID=table1.ID
Left Outer join table1.ID=Table13.ID
In the final select query we can use sum , group by and etc…
The WITH Common Table Expression (CTE) is used for creating temporary resultsets. This functionality indroudeced in SQL SERVER 2005.