In DB2 using CTE,I can do

WITH ZIPCODES (ZIP) AS
(
VALUES ( ('10001'),
('10002'),
('10003')
)
)
select zip from zipcodes

will give

zipcodes
---------
10001
10002
10003


Is it possible to do something similar in SQL Server 2005?

My reqmt is - based on user input in the front-end, I want to build a table like this in CTE and continue to use it multiple times. Just for example :

WITH ZIPCODES (ZIP) AS
(
VALUES ( ('10001'),
('10002'),
('10003')
)
) ,
city as (
select * from citytab where cityzip in (select zip from zipcodes)
)
select * from city,zipcodes, locationid where loczip in (select zip from zipcodes where zip like '1%')



Thanks in advance ...