在asp中如何创建动态表--调用如下sp_executesql

新客网 XKER.COM 2003-07-11 来源： 收藏本文

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

   -----------------------------------    */
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+
        '(
        AnnounceID    int    identity (1, 1)     NOT NULL ,    
        ParentID     int    default (0)        NULL ,        
        Child         int     default (0)        NULL ,        
        User_id         int                 NULL ,        
        boardID        int                NULL ,        
        Topic         nvarchar (255)             NULL ,
        Body         ntext                NULL ,
        DateAndTime     datetime default    (getdate()) NULL ,
        Hits         int    default (0)        NULL ,
        Length        int    default (0)        NULL ,
        RootID         int    default (0)        NULL ,
        Layer         tinyint    default (1)        NULL ,
        Orders         int     default (0)        NULL ,
        Ip         nvarchar (20) default (0)    NULL ,
        Expression     nvarchar (50)             NULL ,
        Forbid         tinyint 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'

        exec sp_executesql @st

• ·ASP+ACCESS数据库中文乱码问题解决
• ·编程实例 字母+数字验证码程序
• ·ASP连接数据库错误解决办法新法
• ·初学:ASP内建对象Response
• ·ASP网站Server object error的解决办法
• ·ASP技巧：让Len,Left,Right函数识别中文
• ·把网页中的电话号码生成图片的ASP程序
• ·ASP如何实现IE地址栏参数的判断
• ·细化解析：ASP连接11种数据库语法集锦
• ·ASP实现网页打开任何类型文件都保存
• ·ASP代码直接增加、删除ACCESS表和字段
• ·ASP：判断访问是否来自搜索引擎的函数
• ·ASP技巧：禁用页面缓存的五种方法
• ·学习ASP关于与变量子类型相关的函数
• ·ASP开发中的(VBScript)类基础学习
• ·ASP访问Excel文件