Create table Stored Procedure

Hi,
Im tring to create a stored procedure with objective as
1) add new project details
2) create table with name as <projectcode>_MONTHSETTINGS
pls correct my below code
thanks in advance
-DNK
---
CREATE PROCEDURE [dbo].[AddNewProject]
(
@.pcod varchar(50) ,
@.pnam varchar(255),
@.keyl varchar(100),
@.cname varchar(255),
@.status varchar(10)
)
as
BEGIN
insert into ORS_PROJECTS
(CODE,PROJECTNAME,KEYLOCATION,CUSTOMERNA
ME,STATUS)
values (@.pcod,@.pnam,@.keyl,@.cname,@.status)
declare @.tabname varchar(255)
@.tabname = @.pcod + "_MONTHSETTINGS"
create table @.tabname (
monthname varchar(50),
targetamount float,
unitcost float )
return @.@.error
END
GODoesnt work for DDL you have tot put it in dynamicSQL:
DECLARE @.SQLSTRING VARCHAR(4000)
SET @.SQLSTRING = 'CREATE TABLE ' + @.tabname '( monthname VARCHAR(50),
targetamount float, unitcost float )'
EXEC(@.SQLSTRING)
http://www.sommarskog.se/dynamic_sql.html
HTH; Jens Suessmeyer.|||you should use dynamic sql to create the table.
"DNKMCA" <dnk@.msn.com> wrote in message
news:OMKqw380FHA.1028@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Im tring to create a stored procedure with objective as
> 1) add new project details
> 2) create table with name as <projectcode>_MONTHSETTINGS
> pls correct my below code
> thanks in advance
> -DNK
> ---
> CREATE PROCEDURE [dbo].[AddNewProject]
> (
> @.pcod varchar(50) ,
> @.pnam varchar(255),
> @.keyl varchar(100),
> @.cname varchar(255),
> @.status varchar(10)
> )
> as
> BEGIN
> insert into ORS_PROJECTS
> (CODE,PROJECTNAME,KEYLOCATION,CUSTOMERNA
ME,STATUS)
> values (@.pcod,@.pnam,@.keyl,@.cname,@.status)
> declare @.tabname varchar(255)
> @.tabname = @.pcod + "_MONTHSETTINGS"
> create table @.tabname (
> monthname varchar(50),
> targetamount float,
> unitcost float )
> return @.@.error
> END
> GO
>|||Why would you create a new table for each project? The obvious solution
would be to have one table for all projects with a project_code column.
David Portas
SQL Server MVP
--|||Most sensible reason is so you can apply different security permissions on
each table.
That way you can restrict project information to the people who are working
on it.
But it doesn't seam to be the case in this instance.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1129631841.002751.28740@.g43g2000cwa.googlegroups.com...
> Why would you create a new table for each project? The obvious solution
> would be to have one table for all projects with a project_code column.
> --
> David Portas
> SQL Server MVP
> --
>|||"Rebecca York" <rebecca.york {at} 2ndbyte.com> wrote in message
news:4354d054$0$137$7b0f0fd3@.mistral.news.newnet.co.uk...
> Most sensible reason is so you can apply different security permissions on
> each table.
> That way you can restrict project information to the people who are
working
> on it.
>
Wouldn't one table with multiple views be the preferred way to handle access
to data?|||Yes. Alternatively, if the requirement is to support a partitioned view
then the project code is almost certainly a bad choice for a
partitioning column. It's unwise to choose a partition that forces
table creation under user control rather than by the administrator.
David Portas
SQL Server MVP
--