[asp] 在asp中如何创建动态表--调用如下sp_e

动态, asp

　　/* -----------------------------------

产生论坛分类目录内容表过程

-----------------------------------*/

CREATE PROCEDURE sp_createnew_bbscontent

@tabname varchar(200)='',

@boardid int

AS

declare @tri_inst_name nvarchar(100)

declare @tri_up_name nvarchar(100)

declare @tri_del_name nvarchar(100)

declare @deltab nvarchar(100)

declare @st nvarchar(2000)

select @tri_inst_name='inst_bbsContent' LTRIM(RTRIM(str(@Boardid)))

select @tri_up_name='up_bbsContent' LTRIM(RTRIM(str(@Boardid)))

select @tri_del_name='delete_bbsContent' LTRIM(RTRIM(str(@Boardid)))

select @deltab='drop table ' @tabname

if len(@tabname)=0

return

if exists (select * from sysobjects where id = object_id(@tabname) and OBJECTPROPERTY(id, N'IsUserTable') = 1)

exec sp_executesql @deltab

select @st='CREATE TABLE ' @tabname

'(

AnnounceIDintidentity (1, 1)NOT NULL ,

ParentIDintdefault (0)NULL ,

Childintdefault (0)NULL ,

User_idintNULL ,

boardIDintNULL ,

Topicnvarchar (255)NULL ,

BodyntextNULL ,

DateAndTimedatetime default(getdate()) NULL ,

Hitsintdefault (0)NULL ,

Lengthintdefault (0)NULL ,

RootIDintdefault (0)NULL ,

Layertinyintdefault (1)NULL ,

Ordersintdefault (0)NULL ,

Ipnvarchar (20) default (0)NULL ,

Expressionnvarchar (50)NULL ,

Forbidtinyint default(0)NULL

)'

exec sp_executesql @st

select @st='CREATE TRIGGER '  @tri_inst_name ' ON ' @tabname  '

FOR INSERT

AS

declare @rid integer,@pid integer

select @pid=ParentId from inserted

if @pid = 0

begin

select @rid =@@identity

update '  @tabname ' set rootid=@rid where AnnounceID=@rid

end'

exec sp_executesql @st

select @st='CREATE TRIGGER '  @tri_up_name ' ON ' @tabname  '

FOR UPDATE

AS

declare @pid int ,@rid int,@forbid tinyint

if update(forbid)

begin

select @pid = parentid,@rid = rootid,@forbid=forbid from inserted

/* 如果其父没有开放则不能开放 */

if exists ( select * from' @tabname  ' where AnnounceID = @pid and Forbid!= 0 )

begin

rollback transaction

return

end

update ' @tabname  ' set forbid=@forbid where rootid=@rid and parentid>@pid

end'

exec sp_executesql @st

select @st='CREATE TRIGGER '  @tri_del_name ' ON ' @tabname  '

FOR DELETE

AS

declare @pid int ,@rid int

select @pid = parentid,@rid = rootid from deleted

delete from ' @tabname  ' where rootid=@rid and parentid>@pid'</dd>

  　　