SQL Stored Procedure - can anyone help plse

[Renners]

Tweet

I'm trying to get a stored procedure to work that will copy over the clickthrough and display details from my main database to a new table named based on (at the monent) the month& & year. I can get the name I need for the table however have lucked out in getting it to create the table simply getting an error when using the variable @MonthYr as the table name.

I've shown my code below, any offers on what the issue is?

Declare @MonthYr varchar(14)
Declare @StatsMonth datetime
Declare @StatsYear varchar(8)

Set @StatsMonth=month(GetDate())
Set @StatsYear=CAST((year(getdate()) % 100) AS CHAR(4))
Set @MonthYr='Stats_'+Cast(@StatsMonth as Varchar(3))+Cast(@StatsYear as varchar(4))


SELECT Prod_ID, Product_Name, Displayed_Count, Clickedcount, validtodate, validfromdate, Date_Added, Suspended
INTO @MonthYr
FROM dibby.tbl_Aff_products

Regards,
Phil.

[jonsp]

I'm trying to get a stored procedure to work that will copy over the clickthrough and display details from my main database to a new table named based on (at the monent) the month& & year. I can get the name I need for the table however have lucked out in getting it to create the table simply getting an error when using the variable @MonthYr as the table name.

I've shown my code below, any offers on what the issue is?

Declare @MonthYr varchar(14)
Declare @StatsMonth datetime
Declare @StatsYear varchar(8)

Set @StatsMonth=month(GetDate())
Set @StatsYear=CAST((year(getdate()) % 100) AS CHAR(4))
Set @MonthYr='Stats_'+Cast(@StatsMonth as Varchar(3))+Cast(@StatsYear as varchar(4))


SELECT Prod_ID, Product_Name, Displayed_Count, Clickedcount, validtodate, validfromdate, Date_Added, Suspended
INTO @MonthYr
FROM dibby.tbl_Aff_products

Regards,
Phil.

You should be able to it with dynamic sql, eg

declare @sql varchar(255)
set @sql = 'select ... into ' + @monthyr + ' from...
exec(@sql)

As you probably know dynamic sql isn't usually the most efficient way to go but it should be fine for this job

Cheers,
Jon

[Renners]

You should be able to it with dynamic sql, eg

declare @sql varchar(255)
set @sql = 'select ... into ' + @monthyr + ' from...
exec(@sql)

As you probably know dynamic sql isn't usually the most efficient way to go but it should be fine for this job

Cheers,
Jon

Thanks Jon, that did it.

Declare @command varchar(500)
set @command='SELECT Prod_ID, Product_Name, Displayed_Count, Clickedcount, validtodate, validfromdate, Date_Added, Suspended
INTO '+@MonthYr+' FROM dibby.tbl_products'
exec(@command)

Regards,
Phil.