SQL Server - With keyword...

Hi,

I am not sure when to use the kyeword "with " in sql.

I also went through its syntax but cant get complete diea of it.

It creates some temp named set of data but am not very much clear.

Can anyone help me with some example?

Actually I also need to fect hierarchial data like employee,its junior,its subjunior & so on..

I think with can be used for it but I am not sure how..

Kindly give some suggestion

WITH in the context you provide specifies a named temporary resultset, also called a CTE (Common Table Expression).

The documentation page on this has many examples to get you started:

http://msdn.microsoft.com/en-us/library/ms175972.aspx

Hi,

The WITH Common Table Expression (CTE) is used for creating temporary named result sets. The functionality was introduced in SQL Server 2005. This T-SQL Expression begins with the keyword WITH. The results from the WITH expression are stored in a temporary named result set that can be queried. Using the WITH expression, allows for the simplification of query logic, by allowing the separation of logic into separate steps. The WITH expression has a few basic parts:

• WITH [name of temporary resultset] (columns in result set)
• AS ( SQL Query Definition )

following the 'WITH' keyword is the name of the temporary result set being created and the names of the columns in the result set. Parentheses are placed around the column names.

following the 'AS' keyword will be the SQL Query (surrounded by parentheses). The number of columns selected in the query must match the number of columns listed in the Table Expression Definition (the columns listed after name of resultset).

Ex:WITH Developers (Name,Salary)
AS
(
SELECT Name, Salary FROM Employees WHERE Position = 'Software Developer'
)

 SELECT * FROM Developers

 If you run the above query, It will give only the two columns selected above,corresponding to the WHERE clause.

~Arun

Ok I have got the meaning of with but I have one more question.

I have something like

WITH expressionname(column names...)

{

select * from table1

union all

select * from table2

}

select X from expression name

In this query,

how would the flow go?

What would be executed first & what next?

Hi,

WITH clause as I mentioned it is for creating temporary data set.

Now firing the below query will lead to

1. It will select all the data from Table 1 and Table 2 with respect to the statement inside the parenthesis.

2.Next it will select the required columnnames you have mentioned from the two tables and it will be stored in the temporary dataset named expressionname.

3. Then if you fire select * from expression name

4. It will display all the items stored with in this dataset.

~Arun

Thnx for reply but thats not true.

In general this works well with Simple union or union all but is not the same when using the with query.

It will first select data from table 1 store it in expression table then will do union with table 2 .After this ,the select query will be executed to select data from final CTE.