Can't you Use a variable as a table name

[rleftw]

I have a question that no one seems to know the answer to, or even if it possible. I am trying to create a stored procedure for a Year End process. What I want to be able to do is have a year input parameter for the procedure that is used to create the name of a new table. What my management wants is separate tables for each year's data. Here is an example of my code:

Create Procedure Test_Leave_YearEnd()
@Yr char(4)
AS
declare @TblNme char(13)

Begin Transaction
SELECT @TblNme = 'Table' + @Yr
SELECT field1, field2, field3, field4
INTO @TblNme
FROM MasterTable
WHERE datepart(yyyy, field4) = @Yr
Commit Transaction

The error I am receiving when I try to parse the procedure is, "Incorrect syntax near @TblNme". This error is on the INTO line. Can't you use a variable for a table name? If not, do you have any suggestions?

 

[Davetoo]

Clearly you're not aware of the forum style used on Tek-Tips. For instance, this is the Windows 2000 Server forum. You seem to be asking a programming question of some sorts. What you need to do is to do a forum search for the programming language that you're using and then post your question there, only after searching the FAQs for that forum as well as doing a keyword search of that forum. Then, if you don't find an answer to your question, you should post it.

I'm Certifiable, not certified.
It just means my answers are from experience, not a book.

 

[rleftw]

I sorry if I enter this in the wrong forum, but I am working within SQL Server 2000 and figured it was the correct place to ask the question.