求一存储过程

[showlin]

嗯，昨天洗澡把头碰坏了，轻微脑震荡，思考不能状态
希望有人帮忙写个过程
要求如下：
入口参数：
1、要查看的页号：
2、每页的纪录数：
3、材料ID：为0表示不指定材料，非0表示指定察看某一特定材料
4、材料代码：模糊匹配
5、所属类别：（注意：需包括其子类别的产品）为0表示所有类别
返回值：
1、取得的产品数
2、返回总页数

库结构如下：

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tb_Product]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tb_Product]
GO

CREATE TABLE [dbo].[Tb_Product] ( -----------------------产品表----
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ProductCode] [Varchar] (20) NULL ,-- 产品代码
[ParentId] [int] NULL ,-- 产品所属类别
[ProductUnit] [Varchar] (20) NULL ,-- 单位
[ProductDesc] [Varchar] (100) NULL -- 说明
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Tb_ProductClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tb_ProductClass]
GO

CREATE TABLE [dbo].[Tb_ProductClass] ( -----------------类别表-----
[Id] [int] IDENTITY (1, 1) NOT NULL ,
[ParentID] [int] NUll,--- 类别的父类 1级类别为0
[ProductInfo] [Varchar] (200) NULL, -- 类别说明
[Level] int Null --类别层次
) ON [PRIMARY]
GO

[showlin]

tb_product没有少任何字段

[showlin]

过程功能是返回符合条件的材料列表
注意：
1、材料ID非0状态下，用来取特定的材料，此时，下面的类别、代码等参数基本可忽略
2、材料ID为0、类别参数非0时，要注意取得是该类子树下所有产品，比如
5
|-------6
|-----8
|-----9
|------7
传入5，则必须取所有产品满足parentid=5 or parentid=6 or parentid=7 or parentid=8 or parentid=9
3、level取值范围为1-65535

[-狙击手-]

给你一个分页的例子

http://topic.csdn.net/t/20031017/11/2365596.html

[showlin]

麻烦先看要求，分页偶会，主要是类别树，谢谢

[-狙击手-]

不好意思，贴点数据，我来测试一下

[mislrb]

贴点数据出来，帮你想想，呵呵

[小辉]

这里第三个问题 就是你要的
你参考一下这个
http://publish.it168.com/2005/1127/20051127009601.shtml?positioncode=1547

某棵子树所有子节点信息
　　
　　前面的两种查询返回的都是标量值，这里的查询需返回某棵子树的所有子节点的信息，这是一个结果集，需要用 table 数据类型来存储。函数定义如下：
　　
　　CREATE FUNCTION dbo.GetSubtreeInfo
　　
　　( @manager_id AS int
　　
　　)
　　
　　RETURNS @treeinfo table
　　
　　( [员工号码] [char] (5) NOT NULL,
　　
　　[姓名] [char] (10) NOT NULL,
　　
　　[年龄] [int] NOT NULL,
　　
　　[工资] [money] NOT NULL,
　　
　　[上级号码] [char] (5) NULL，
　　
　　[级别] [int] NOT NULL
　　
　　)
　　
　　其中，@manager_id 代表要查询的上司的员工号码，返回的是其所有下属的信息，这些信息存放在 table 型变量 @treeinfo 中。
　　
　　由于该查询返回的是一个结果集，因此已经不能使用递归的方法来实现，我们使用循环的方法来实现，循环的过程为：将参数 @manager_id 所代表的上司的信息插入到表中，赋予级别0；级别增加为1，将所有上级号码为以上 @manager_id 的员工信息插入到表中；级别增加为2，将所有上级号码与第2步插入的记录中的员工号码一致的员工信息插入到表中；依次增加级别，直到找不到上级号码与前一步插入的纪录中的员工号码一致的员工信息为止。
　　
　　为了实现这个循环，我们要用系统函数 @@ROWCOUNT 来判断前一步中是否有新的记录被插入到表中。如果有，则循环继续；如果无，则循环结束。另外，我们在表中增加了一个名为“级别”的字段，既可以显示出所在的级别关系，还可以用来代表每一次新插入的记录，可谓一举两得。完整的函数定义如下：
　　
　　CREATE FUNCTION dbo.GetSubtreeInfo
　　
　　( @manager_id AS char(5)
　　
　　)
　　
　　RETURNS @treeinfo table
　　
　　( [员工号码] [char] (5) NOT NULL,
　　
　　[姓名] [char] (10) NOT NULL,
　　
　　[年龄] [int] NOT NULL,
　　
　　[工资] [money] NOT NULL,
　　
　　[上级号码] [char] (5) NULL,
　　
　　[级别] [int] NOT NULL
　　
　　) AS
　　
　　BEGIN
　　
　　DECLARE @level AS int
　　
　　SELECT @level = 0
　　
　　INSERT INTO @treeinfo
　　
　　SELECT [员工号码], [姓名], [年龄], [工资], [上级号码], @level
　　
　　FROM [员工信息]
　　
　　WHERE [员工号码] = @manager_id
　　
　　WHILE @@ROWCOUNT > 0
　　
　　BEGIN
　　
　　SET @level = @level + 1
　　
　　INSERT INTO @treeinfo
　　
　　SELECT E.[员工号码], E.[姓名], E.[年龄], E.[工资], E.[上级号码], @level
　　
　　FROM [员工信息] AS E JOIN @treeinfo AS T
　　
　　ON E.[上级号码] = T.[员工号码] AND T.[级别] = @level - 1
　　
　　END
　　
　　RETURN
　　
　　END
　　
　　下面是测试的结果：
　　
　　SELECT * FROM dbo.GetSubtreeInfo(‘E9903’)
　　
　　员工号码 姓名 年龄 工资 上级号码 级别
　　
　　-------- --------- ------- --
　　
　　E9903 郑可可 38 5000.0000 E9901 0
　　
　　E9906 肖遥 26 3350.0000 E9903 1
　　
　　E9907 黄菁菁 22 2800.0000 E9906 2

[showlin]

还要数据阿，偶也没有~~~~~掰吧


insert into [dbo].[Tb_ProductClass] (ParentID,ProductInfo,[Level]) values (0,'1级类别1',1)
insert into [dbo].[Tb_ProductClass] (ParentID,ProductInfo,[Level]) values (0,'1级类别2',1)
insert into [dbo].[Tb_ProductClass] (ParentID,ProductInfo,[Level]) values (1,'2级类别1-1',2)
insert into [dbo].[Tb_ProductClass] (ParentID,ProductInfo,[Level]) values (1,'2级类别1-2',2)
insert into [dbo].[Tb_ProductClass] (ParentID,ProductInfo,[Level]) values (2,'2级类别2-1',2)
insert into [dbo].[Tb_ProductClass] (ParentID,ProductInfo,[Level]) values (3,'3级类别1-1-1',3)

insert into [dbo].[Tb_Product] (ProductCode,ParentID) values ('产品1',1)
insert into [dbo].[Tb_Product] (ProductCode,ParentID) values ('产品2',2)
insert into [dbo].[Tb_Product] (ProductCode,ParentID) values ('产品1-1',3)
insert into [dbo].[Tb_Product] (ProductCode,ParentID) values ('产品1-2',4)
insert into [dbo].[Tb_Product] (ProductCode,ParentID) values ('产品2-1',5)
insert into [dbo].[Tb_Product] (ProductCode,ParentID) values ('产品1-1-1',6)

[mislrb]

我觉得楼主的问题有些。。。。。
是不是本身你也没考虑清楚怎么做？

如果你查询 like '%产品%',哪么依据你给的测试数据，tb_product表的所有记录将会查出，并且也会查出其子，这样会有相当数量的记录重复
--
3、材料ID：为0表示不指定材料，非0表示指定察看某一特定材料
4、材料代码：模糊匹配
5、所属类别：（注意：需包括其子类别的产品）为0表示所有类别
---------------------
如果满足第3点，第4点，第5点会不会是多余条件，如果这几个条件有冲突，哪要依据哪一个？
这个问题有点头大

贴上部份代码，因为觉得问题中存在问题，到时可能是白忙，呵呵

insert Tb_Product
select '1000',null,'PCS','test1' union all
select '1000-01',1,'PCS','test2' union all
select '1000-02',1,'PCS','test3' union all
select '1000-01-01',2,'PCS','test4' union all
select '1000-01-01-01',4,'PCS','test5' union all
select '1000-01-02-01',4,'PCS','test6' union all
select '1000-02-01',3,'PCS','test7' union all
select '1000-02-02',3,'PCS','test8' union all
select '2000',null,'PCS','test9' union all
select '2000-01',9,'PCS','test10' union all
select '2000-02',9,'PCS','test11' union all
select '2000-01-01',10,'PCS','test12' union all
select '2000-01-01-01',12,'PCS','test13' union all
select '2000-01-02-01',12,'PCS','test14' union all
select '2000-01-02-01-01',14,'PCS','test15' union all
select '2000-02-02',11,'PCS','test16'

insert Tb_ProductClass
select null,'1000',0 union all
select 1,'1000-01',1 union all
select 1,'1000-02',1 union all
select 2,'1000-01-01',2 union all
select 4,'1000-01-01-01',3 union all
select 4,'1000-01-02-01',3 union all
select 3,'1000-02-01',2 union all
select 3,'1000-02-02',2 union all
select null,'2000',0 union all
select 9,'2000-01',1 union all
select 9,'2000-02',1 union all
select 10,'2000-01-01',2 union all
select 12,'2000-01-01-01',3 union all
select 12,'2000-01-02-01',3 union all
select 14,'2000-01-02-01-01',4 union all
select 11,'2000-02-02',2

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_getResult]') and xtype=N'TF')
drop function [dbo].[f_getResult]
GO
create function f_getResult(@page int,@numperpage decimal(18,1),@id int,@productcode varchar(20),@productclass int)
returns @t table(product_num int,total_page int)
as
begin
declare @tt table([Id] [int] IDENTITY (1, 1) NOT NULL ,
[productid] int,
[ProductCode] [Varchar] (20) NULL ,-- 产品代码
[ParentId] [int] NULL ,-- 产品所属类别
[ProductUnit] [Varchar] (20) NULL ,-- 单位
[ProductDesc] [Varchar] (100) NULL,
[level] int) --
declare @i int --,@productcode varchar(20)
select @i=0 --,@productcode='1000'
insert @tt select *,@i from tb_product where productcode like '%'+@productcode+'%'

while @@rowcount>0
begin
set @i=@i+1
insert @tt
select a.id,a.productcode,a.parentid,a.productunit,a.productdesc,@i
from tb_product a,@tt b
where a.[parentid]=b.[productid] and b.[level]=@i-1
end

insert @t select max(id),ceiling(max(id)/@numperpage) from @tt
return
end

select * from dbo.f_getresult(1,3.0,0,'1000',0)

--结果
/*
product_num total_page
----------- -----------
22 8
*/

[showlin]

不好意思，我再解释清楚一些，头痛得厉害~~~~~，麻烦各位了
3是一个单独的条件，是为了取详细产品资料而放在这里的，它的级别最高，会用到它的时候，前两个参数也就会是1，1
而4和5是and的关系
基本上应该是这样的
if @Productid<>0
select * from Tb_product where id=@ProductID --取单一产品
else
begin
if @ClassID=0
select * from Tb_Product where ProductCode like '%' + @ProductCode+ '%' --取所有类别产品
else
begin
------开始取某类别下的所有产品
----------------
end
end

[showlin]

应该可以参照 crazy_boy1(小辉) 的方法构造出sql语句 where 之后的条件，然后调用P_EXECUTESQL吧？

[mislrb]

再就是你这两个表的关系，tb_product是不是少了个字段

[showlin]

意思是tb_Product里还要个level字段来反映深度？