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?
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?