Does SQL Server 2000 has a 'WITH' CLAUSE

• kam7395

  Old Hand

  Points: 395

  More actions

  June 12, 2009 at 10:00 am

  #222462

  Hello All,

  Please anybody let me know does SQL Server 2000 supports "WITH" clause. If no, then what's the substitue for "WITH" clause.

  Thank You

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 12, 2009 at 10:09 am

  #1008781

  do you mean using the WITH clause with a Common Table expression, or do you mean where you use the WITH clause to use a hint, like WITH(TABLOCKX)?

  a CTE would get replaced with either a subquery or a temp table.

  SELECT * FROM ([SELECT * FROM The_sql_that_used_to_be_in_a_CTE)

  a hint featuring the WITH works in SQL2000 but was optional, and in 2005 it the WITH keyword became manditory:

  select * from Invoices WITH(TABLOCKX)

  select * from Invoices TABLOCKX

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• kam7395

  Old Hand

  Points: 395

  More actions

  June 12, 2009 at 11:11 am

  #1008820

  I mean "with" clause with a common_table_expression in SQL Server 2000.

  e.g. can I write as follows:

  with x (startdate, enddate)

  as

  (

  select ............

  )

• Jeff Moden

  SSC Guru

  Points: 1004372

  More actions

  June 12, 2009 at 9:39 pm

  #1008988

  No... SQL Server 2000 doesn't have WITH/CTE. Instead, use a "Derived Table" which is nearly identical in most cases. A derived table is just like a CTE in that it's a query that's used in a FROM clause as if its result set were a table. In other words, it's a subquery in the FROM clause just like a CTE is a subquery in the WITH clause. The format is...

  [font="Courier New"] SELECT d.yada-yada

  FROM (--===== derived table starts here

  SELECT yada-yada

  FROM sometable(s)

  ) d[/font]

  In the above example, "d" is the table alias for the derived table.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)