Dynamically create table on different filegroup 1

[hmckillop]

Ok, I have put this into simplier terms in order to facilitate an answer, but in short I am trying to complete the sliding window scenario on using multiple filegroups (Kimberly Tripp conviently uses the hardcoded same filegroup in her examples).

I have a proc which will create a specified table on a specified filegroup.
It works ok for the specified table (if used on its own with a hardcoded filegroup), but not if a dynamic filegroup is used. How do i solve this problem -

CREATE proc usp_test (@tablename varchar(50), @tablegroup varchar(100))
as
begin

CREATE TABLE [dbo].[@tablename] (
	[OID] [ObjectID] IDENTITY (1, 1) NOT NULL ,
	[CreatedDate] [datetime] NOT NULL ,
	[Version] [datetime] NOT NULL ,
	[AccountOID] [ObjectID] NOT NULL ,
	[TransactionDate] [datetime] NOT NULL ,
	[SequenceNumber] [int] NOT NULL ,
	[TransactionType] [MnemonicString] NOT NULL ,
	[Description] [varchar] (80) COLLATE Latin1_General_CI_AS NOT NULL ,
	[ValueDate] [datetime] NULL ,
	[Amount] [FinancialAmount] NULL

-- Not working OLLY

) ON [@tablegroup]
end
--exec usp_test 'AccountTransactionStagingTable', 'FG_1'

Any help much appreciated,
Thanks

I am probably missing something very straight forward, but my head hurts (a[URL unfurl="true"]www!)[/URL] and cant find reference in help anywhere - I have used sp_executesql etc, but to no avail.




"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLDenis]

you need dynamic SQL

CREATE proc usp_test (@tablename varchar(50), @tablegroup varchar(100))
as
begin

declare @SQL varchar(8000)
select @SQL = '
CREATE TABLE [dbo].[' + @tablename + '] (
    [OID] [ObjectID] IDENTITY (1, 1) NOT NULL ,
    [CreatedDate] [datetime] NOT NULL ,
    [Version] [datetime] NOT NULL ,
    [AccountOID] [ObjectID] NOT NULL ,
    [TransactionDate] [datetime] NOT NULL ,
    [SequenceNumber] [int] NOT NULL ,
    [TransactionType] [MnemonicString] NOT NULL ,
    [Description] [varchar] (80) COLLATE Latin1_General_CI_AS NOT NULL ,
    [ValueDate] [datetime] NULL ,
    [Amount] [FinancialAmount] NULL

-- Not working OLLY

) ON [' + @tablegroup +']'

--exec (@SQL)
print @SQL
end

exec usp_test 'AccountTransactionStagingTable', 'FG_1'

uncomment the exec once you are ready for testing





Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[hmckillop]

You know what, I had tried that but it still didnt work, only now realising a very stupid mistake.
So much so that I wont even post it.
Thanks for help

"I'm living so far beyond my income that we may almost be said to be living apart