You may also want to think about making a reference table which consist of your distinct years.  How you will keep that table current should be thought through as updating...

I have difficulty buying that answer. 

Hopefully, some of the true gurus will see this post and give us a better review and explaination. ...

Try playing with this... It gives you more information than you want...

SET NOCOUNT ON

SELECT name AS TableName

INTO #TableName

FROM sysobjects

WHERE...

You may need a more simple remedy.  Try using executesql.  Here is a simple little script, (which loops; something you may not need) that uses executesql and may help you...

I cannot seem to replicate this problem.  Here is the simple script I used, can you correct anything I have misinterpretted? 

SELECT 'TEST     ' AS rfqName, 1 AS rfq, 7500...

I would suggest looking up INFORMATION_SCHEMA in Books OnLine (BOL).  Here is a simple example of things it can offer you.  You may find you will need to use other...

I am not completely clear as to where you want these results.  You are choosing two fields from a table as a recordset, and want one @Variable to hold them? ...

Go to Enterprise Manager and open the database you want.  Tables will be one of the icons.  Click on that and on the right-hand side of the screen you will...

This might be quicker. 

SELECT SUBSTRING( CONVERT( varchar, DATEADD( dd, -1, GETDATE()), 101), 1, 2) + '_' + 

 SUBSTRING( CONVERT( varchar, DATEADD( dd, -1, GETDATE()), 101), 4, 2) + '_'...

I think this will work.  I have replaced GETDATE() with @TestDate so you can try different dates to make sure this works. 

DECLARE...

Congradulations Frank!  I would have loved to have had more than one.    It is the old Bill Cosby saying with me:  "People with...

I do not know your tables to give you a specific suggestion.  I would search this site for "dynamic" SQL.  I would especially suggest you pay attention to the dangers...

Did ISNULL ( check_expression , replacement_value ) not work? 

Change  'WHILE @Counter <= 10  ' + CHAR(13) + 

to read as

 'WHILE @Counter >= 1  ' + CHAR(13) +

and it should run forever....  until you "Kill" it. 

Definately the way to do it. 

Select those values into a #TempTable using the WHERE NOT EXISTS(.... clause, then select all values from the #TempTable converting them back to varchar. ...