Stored Procedure

[Esoteric]

I have a process that every 1st of the month creates a new table ie: CR200602 for Feb, 2006. I need to put a trigger on this table, not a problem, but then come March I have to do it again. So here is what I want to do.

Create a stored procedure, got that part done, but I cannot put in this:

CREATE TRIGGER trigger_name ON @NewTableName FOR INSERT

I have made these variables:

Set @Year = year(getdate())
Set @Month = cast (datepart(mm,getdate()) as varchar(2))
Set @NewTableName = 'CR' & @Year & @Month

This part works but I cannot get the variable into the CREATE statement.

 

[SQLDenis]

you need dynamic SQl for that
declare @year int, @month int,@NewTableName varchar(100)
Set @Year = year(getdate())
Set @Month = cast (datepart(mm,getdate()) as varchar(2))
Set @NewTableName = 'CR' + convert(varchar,@Year) + convert(varchar,@Month)
declare @SQl varchar (500)
select @SQL ='Create table ' + @NewTableName + ' rest of code here '

print @SQL -- to tes
exec (@SQL)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

You'll have to use dynamic SQL and EXEC/sp_executesql to do that.

Btw. instead of

Set @Month = cast (datepart(mm,getdate()) as varchar(2))

better use:

select right('0' + cast (datepart(mm,getdate()) as varchar(2)), 2)

(hint: fixed length, leading zero)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)