sql server programming: [quoted text, click to view]

Faye,

You can use dynamic sql like below.

declare @sql varchar(5000)
declare @i int

set @i=2000
while @i<2008
begin

set @sql='select * from year_'+cast(@i as varchar(10))
exec(@sql)

set @i=@i+1

end


Wei

Why would you want to loop? SQL is set based so how about:

SELECT <ColumnList>
FROM <DataTable> AS DT
JOIN tblYear AS Y ON DT.myYear = Y.Year

Replace <DataTable> with the name of the main table you are querying
against, and obviously you may need to update the join condition to use the
actual names of the columns in the tables involved.

An alternate solution would be to use start and end year variables:

DECLARE @StartYear INT
DECLARE @EndYear INT

SET @StartYear = 2000
SET @EndYear = 2006

SELECT <ColumnList>
FROM <DataTable>
WHERE myYear BETWEEN @StartYear AND @EndYear

Now you've avoided using a nasty loop and/or dynamic SQL, both of which you
should avoid using if at all possible.

I hope I understood your problem.

Michael MacGregor
Database Architect

[quoted text, click to view]

I think something was lost in translation, and that dynamic SQL is not
needed here. It is not the column names that are variable but rather the
variables in the (now missing) where clause.

This following stored procedure works fine,

SELECT ....
WHERE myYear = @searchYear
UNION
SELECT...
myYear = @searchYear
UNION
SELECT
myYear = @searchYear

What I want to do is to set up a loop function and run the above sp
for each year available from a table tblYear within this function. I
don't know how to do a for loop in sql. I appreciate any input.
Thanks.

Faye