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

您的位置：首页 → 技术开发 → ASP技巧 → 在asp中如何创建动态表--调用如下sp_execute

在asp中如何创建动态表--调用如下sp_execute 时间：2004/11/7 4:12:00来源：本站整理作者：蓝点我要评论(0): /* -----------------------------------

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

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

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'

文章评论

查看所有0条评论>>

热门文章 没有查询到任何记录。