Using the "With Clause" SQL Server 2008

Question

Can someone show me a sample SQL server script that I can look at that uses the "With Clause"?

I am trying to use this clause to iterate through 200 databases that contain the same table that I am trying to run a query on. I am trying to avoid using a cursor because the query time takes too long as well as using a while a loop.

Can someone advise me as to what I can do.

Thank you.

Answer 1

Just a poke, but here's another way to write FizzBuzz :) 100 rows is enough to show the WITH statement, I reckon.

;WITH t100 AS (  SELECT n=number  FROM master..spt_values  WHERE type='P' and number between 1 and 100 )                  SELECT     ISNULL(NULLIF(     CASE WHEN n % 3 = 0 THEN 'Fizz' Else '' END +     CASE WHEN n % 5 = 0 THEN 'Buzz' Else '' END, ''), RIGHT(n,3))  FROM t100 

But the real power behind WITH (known as Common Table Expression http://msdn.microsoft.com/en-us/library/ms190766.aspx "CTE") in SQL Server 2005 and above is the Recursion, as below where the table is built up through iterations adding to the virtual-table each time.

;WITH t100 AS (  SELECT n=1  union all  SELECT n+1  FROM t100  WHERE n < 100 )                  SELECT     ISNULL(NULLIF(     CASE WHEN n % 3 = 0 THEN 'Fizz' Else '' END +     CASE WHEN n % 5 = 0 THEN 'Buzz' Else '' END, ''), RIGHT(n,3))  FROM t100 

To run a similar query in all database, you can use the undocumented sp_msforeachdb. It has been mentioned in another answer, but it is sp_msforeachdb, not sp_foreachdb.

Be careful when using it though, as some things are not what you expect. Consider this example

exec sp_msforeachdb 'select count(*) from sys.objects' 

Instead of the counts of objects within each DB, you will get the SAME count reported, begin that of the current DB. To get around this, always "use" the database first. Note the square brackets to qualify multi-word database names.

exec sp_msforeachdb 'use [?]; select count(*) from sys.objects' 

For your specific query about populating a tally table, you can use something like the below. Not sure about the DATE column, so this tally table has only the DBNAME and IMG_COUNT columns, but hope it helps you.

create table #tbl (dbname sysname, img_count int);  exec sp_msforeachdb ' use [?]; if object_id(''tbldoc'') is not null insert #tbl select ''?'', count(*) from tbldoc'  select * from #tbl 

Answer 2

There are two types of WITH clauses:

Here is the FizzBuzz in SQL form, using a WITH common table expression (CTE).

;WITH mil AS (
 SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY c.column_id ) [n]
 FROM master.sys.all_columns as c
 CROSS JOIN master.sys.all_columns as c2
)                
 SELECT CASE WHEN n  % 3 = 0 THEN
             CASE WHEN n  % 5 = 0 THEN 'FizzBuzz' ELSE 'Fizz' END
        WHEN n % 5 = 0 THEN 'Buzz'
        ELSE CAST(n AS char(6))
     END + CHAR(13)
 FROM mil

Here is a select statement also using a WITH clause

SELECT * FROM orders WITH (NOLOCK) where order_id = 123

Answer 3

Try the sp_foreachdb procedure.