if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_del]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[cwl_sp_sort_del] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_return_childs]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[cwl_sp_sort_return_childs] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_brer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[cwl_sp_sort_select_brer] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_child]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[cwl_sp_sort_select_child] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_parents]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[cwl_sp_sort_select_parents] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_root]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[cwl_sp_sort_select_root] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sp_sort_select_tree]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[cwl_sp_sort_select_tree] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[cwl_sort_kernel]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[cwl_sort_kernel] GO
CREATE TABLE [dbo].[cwl_sort_kernel] ( [guid] [int] IDENTITY (1, 1) NOT NULL , [s_id] [int] NOT NULL , [s_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [s_parents] [varchar] (250) COLLATE Chinese_PRC_CI_AS NOT NULL , [s_childs] [text] COLLATE Chinese_PRC_CI_AS NOT NULL , [s_fid] [int] NOT NULL , [s_star] [int] NOT NULL , [channelid] [int] NOT NULL , [userid] [int] NOT NULL , [s_order] [varchar] (5) COLLATE Chinese_PRC_CI_AS NULL , [s_ordertree] [varchar] (250) COLLATE Chinese_PRC_CI_AS NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
ALTER TABLE [dbo].[cwl_sort_kernel] WITH NOCHECK ADD CONSTRAINT [PK_CWL_SORT_KERNEL_guid] PRIMARY KEY CLUSTERED ( [guid] ) ON [PRIMARY] GO
ALTER TABLE [dbo].[cwl_sort_kernel] ADD CONSTRAINT [DF_CWL_SORT_KERNEL_c_id] DEFAULT (0) FOR [s_id], CONSTRAINT [DF_CWL_SORT_KERNEL_c_name] DEFAULT (N'') FOR [s_name], CONSTRAINT [DF_cwl_sort_kernel_s_parents] DEFAULT (N'') FOR [s_parents], CONSTRAINT [DF_cwl_sort_kernel_s_childs] DEFAULT (N'') FOR [s_childs], CONSTRAINT [DF_cwl_sort_kernel_c_fid] DEFAULT (0) FOR [s_fid], CONSTRAINT [DF_cwl_sort_kernel_c_star] DEFAULT (1) FOR [s_star], CONSTRAINT [DF_cwl_sort_kernel_channelid] DEFAULT (0) FOR [channelid], CONSTRAINT [DF_cwl_sort_kernel_userid] DEFAULT (0) FOR [userid] GO
CREATE INDEX [IX_CWL_SORT_KERNEL_parents] ON [dbo].[cwl_sort_kernel]([s_parents]) ON [PRIMARY] GO
CREATE INDEX [IX_cwl_sort_kernel_fid] ON [dbo].[cwl_sort_kernel]([s_fid]) ON [PRIMARY] GO
CREATE INDEX [IX_cwl_sort_kernel_star] ON [dbo].[cwl_sort_kernel]([s_star]) ON [PRIMARY] GO
CREATE INDEX [IX_cwl_sort_kernel_channelid] ON [dbo].[cwl_sort_kernel]([channelid]) ON [PRIMARY] GO
CREATE INDEX [IX_cwl_sort_kernel_userid] ON [dbo].[cwl_sort_kernel]([userid]) ON [PRIMARY] GO
CREATE INDEX [IX_cwl_sort_kernel_id] ON [dbo].[cwl_sort_kernel]([s_id]) ON [PRIMARY] GO
CREATE INDEX [IX_cwl_sort_kernel_order] ON [dbo].[cwl_sort_kernel]([s_order]) ON [PRIMARY] GO
CREATE INDEX [IX_cwl_sort_kernel_ordertree] ON [dbo].[cwl_sort_kernel]([s_ordertree]) ON [PRIMARY] GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
/*通用分类存储过程*/ --1、删除分类 CREATE PROCEDURE cwl_sp_sort_del @userid int, @channelid int, @s_id int AS DECLARE @s_childs varchar(7896), @s_parents varchar(250), @SQL varchar(8000) BEGIN SELECT @s_childs = s_childs,@s_parents = s_parents FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid SET @SQL = 'DELETE FROM dbo.cwl_sort_kernel WHERE channelid = '+cast(@channelid as varchar(8))+' AND userid = '+cast(@userid as varchar(8))+' AND s_id IN ('+CAST(@s_childs AS varchar(7896))+')' EXECute (@SQL) SET @SQL = 'UPDATE dbo.cwl_sort_kernel SET s_childs = REPLACE(s_childs,'',''+s_id,'') WHERE channelid = '+cast(@channelid as varchar(8))+' AND userid = '+cast(@userid as varchar(8))+' AND s_id IN ('+CAST(@s_parents AS varchar(250))+')' EXECute (@SQL) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
/*通用分类存储过程*/ --5、该大类的所有后代 取内容列表 CREATE PROCEDURE cwl_sp_sort_return_childs @userid int, @channelid int, @s_id int, @s_childs varchar(7896) output AS --set nocount on SELECT @s_childs = s_childs FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO
/*通用分类存储过程*/ --1、兄弟类 CREATE PROCEDURE cwl_sp_sort_select_brer @userid int =0, @channelid int = 0, @s_id int AS DECLARE @s_fid int, @SQL varchar(300) SELECT @s_fid = s_fid FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND channelid = @channelid AND userid = @userid SET @SQL = 'SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = '+CAST(@channelid AS varchar(8))+' AND userid = '+CAST(@userid AS varchar(8))+' AND s_fid = '+CAST(@s_fid AS varchar(8))+' ORDER BY s_order' exec (@SQL) GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
/*通用分类存储过程*/ --1、子类 CREATE PROCEDURE cwl_sp_sort_select_child @userid int, @channelid int, @s_id int AS SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE s_fid = @s_id AND channelid = @channelid AND userid = @userid ORDER BY s_order GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
/*通用分类存储过程*/ --1、继承 CREATE PROCEDURE cwl_sp_sort_select_parents @userid int =0, @channelid int = 0, @s_id int AS DECLARE @s_parents varchar(250),@SQL varchar(1000) BEGIN SELECT @s_parents = s_parents FROM dbo.cwl_sort_kernel WHERE s_id = @s_id AND userid = @userid AND channelid = @channelid SET @SQL = 'SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = '+CAST(@channelid AS varchar(8))+' AND userid = '+CAST(@userid AS varchar(8))+' AND s_id in ('+@s_parents+') ORDER BY s_parents' EXEC (@SQL) END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
/*通用分类存储过程*/ --1、根类 CREATE PROCEDURE cwl_sp_sort_select_root @userid int =0, @channelid int = 0 AS SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid AND s_star = 1 ORDER BY s_order GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS OFF GO
/*通用分类存储过程*/ --1、分类树 CREATE PROCEDURE cwl_sp_sort_select_tree @userid int =0, @channelid int = 0, @s_star int = 0 AS if @s_star = 0 SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid ORDER BY s_ordertree else SELECT guid, s_id, s_name, s_parents, s_fid, s_star FROM dbo.cwl_sort_kernel WHERE channelid = @channelid AND userid = @userid AND s_star <= @s_star ORDER BY s_ordertree GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
exec sp_addextendedproperty N'MS_Description', N'分类归属频道', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N'channelid' GO exec sp_addextendedproperty N'MS_Description', N'子,包括自己和所有延伸', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_childs' GO exec sp_addextendedproperty N'MS_Description', N'上级id', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_fid' GO exec sp_addextendedproperty N'MS_Description', null, N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_id' GO exec sp_addextendedproperty N'MS_Description', N'分类名称', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_name' GO exec sp_addextendedproperty N'MS_Description', N'继承关系,由根到自己本身', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_parents' GO exec sp_addextendedproperty N'MS_Description', N'当前级数', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N's_star' GO exec sp_addextendedproperty N'MS_Description', N'分类归属用户', N'user', N'dbo', N'table', N'cwl_sort_kernel', N'column', N'userid'
GO |
|
|