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.
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 yearFor example:CREATE PROCEDURE [dbo].[usp_xyz] ASBEGINselect count(*) , column1,column2 FROM [dbo].[AK_data_Nov2010]ENDFrom 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]ASBEGINDeclare @SQL varchar(1000)Declare @date datetimeDeclare @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,column2FROM [dbo].[AK_data' + @strdate + ']GROUP BY column1,column2'--Exec sp_executeSQL @SQLPrint @SQLENDyou 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. |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-13 : 17:45:17
|
Here's another waySELECT LEFT(DateName(month, DateAdd(month, -1, getdate())), 3) + Convert(char(4), DatePart(year, DateAdd(month, -1, getdate()))) |
|
|
tpavan7329
Starting Member
18 Posts |
Posted - 2011-01-17 : 15:30:26
|
Thanks, it worksT Pavan Kumar |
|
|
|
|
|
|
|