Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Change table name based on the month in sql server

Author  Topic 

tpavan7329
Starting Member

18 Posts

Posted - 2011-01-13 : 17:24:35
Hi,
I was trying to find a way to change the table name inside a stored procedure based on previous month and current year

For example:

CREATE PROCEDURE [dbo].[usp_xyz]

AS
BEGIN
select count(*) , column1,column2
FROM [dbo].[AK_data_Nov2010]
END

From the above script i want to change the table name 'AK_Data_Nov2010' automatically when i execute the stored procedure to get previous month's first three characters and year in YYYY(Note: creation of table is done automatically every month.)

Can anyone help me to find a way to do it without passing the table name as a parameter?

T Pavan Kumar

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-13 : 17:43:30
CREATE PROCEDURE [dbo].[usp_xyz]

AS
BEGIN
Declare @SQL varchar(1000)
Declare @date datetime
Declare @strdate char(7)
SET @date = dateadd(d,-day(getdate()),getdate())
Set @strdate = convert(varchar(7),
left(datename(month,@date),3)+
convert(varchar(4),year(@date)))

SELECT @SQL = '
select count(*) , column1,column2
FROM [dbo].[AK_data' + @strdate + ']
GROUP BY column1,column2'
--Exec sp_executeSQL @SQL
Print @SQL
END

you can use dynamic SQL, but having different table names for each month indicates a design flaw.



Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-01-13 : 17:45:17
Here's another way
SELECT	LEFT(DateName(month, DateAdd(month, -1, getdate())), 3) +
Convert(char(4), DatePart(year, DateAdd(month, -1, getdate())))
Go to Top of Page

tpavan7329
Starting Member

18 Posts

Posted - 2011-01-17 : 15:30:26
Thanks, it works

T Pavan Kumar
Go to Top of Page
   

- Advertisement -