the purpose of a "WITH" clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

the purpose of a "WITH" clause

Author

Topic

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-09-25 : 11:28:46

I am looking through some code on a new project I have been brought in on.

It has a query that uses the WITH clause, but I van't figure out what it is actually doing.

It is an INSERT SELECT with the WITH clause at the end like this:
WITH (
variable char(1) 'variable',
variable2 char(2) 'variable2')

etc.

What I don't understand is that the variables being declared have a length of one or two, but the default value being assigned is longer.

This is production code so I know it works, I just don't know what is going on.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-25 : 11:30:03

WITH denotes the start of a Common Table Expression (cte), which can be interpreted as a "miniview" or "very local view".
It can also be used together with some XML coding.

We don't see enough of your code to make a clever assumption.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:30:24

Its called common table expressions. have a look at books online about CTEs and their uses or refer below link

http://www.databasejournal.com/features/mssql/article.php/3502676

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-25 : 11:36:08

or it may be specifying the schema (fields you want) while reading from XML using OPENXML as in below example.

http://msdn.microsoft.com/en-us/library/aa276847(SQL.80).aspx

chedderslam
Posting Yak Master

223 Posts

Posted - 2008-10-07 : 09:37:02

Thanks for the links, guys. CTEs look interesting.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-07 : 09:42:19