得到最终满足符合品牌名和小分类名的产品标识

--根据小类名称和品牌名来取客户信息
declare @brandname nvarchar(50)
declare @categoryname nvarchar(50) ---分类的中文名

 

set @categoryname='空调'
set @brandname='海尔'

--取得ClassID
declare @categoryid nvarchar(15)
if OBJECT_ID('Tempdb.dbo.#categoryids') is not null
begin
print 'Exists Table Tempdb.dbo.#categoryids'
end
else
begin
print 'Not Exists Table Tempdb.dbo.#categoryids'
create Table #categoryids(
[categoryid] [nvarchar](15) not null
)
end
declare categoryidsCursor cursor for
select ClassID from FS_MS_ProductsClass where ClassCName=@categoryname
open categoryidsCursor
fetch categoryidsCursor into @categoryid
while @@FETCH_STATUS=0
begin
insert into #categoryids (categoryid) values (@categoryid)
fetch next from categoryidsCursor into @categoryid
end
close categoryidsCursor
deallocate categoryidsCursor
select distinct * from #categoryids

 

--取得品牌ID
declare @brandid char(3)
if OBJECT_ID('Tempdb.dbo.#brandids') is not null
begin
print 'Exists Table Tempdb.dbo.#brandids'
--drop table #categoryids
end
else
begin
print 'Not Exists Table Tempdb.dbo.#brandids'
create table #brandids(
[bid] [char](3) not null
)
end
declare brandidsCursor cursor for
select bid from FS_MS_Brand where brandname=@brandname
open brandidsCursor
fetch brandidsCursor into @brandid
while @@FETCH_STATUS=0
begin
insert into #brandids (bid) values (@brandid)
fetch next from brandidsCursor into @brandid
end
close brandidsCursor
deallocate brandidsCursor
select distinct * from #brandids

 

declare @bid char(3)
declare @classid nvarchar(15)
declare @prdtid char(7)
if OBJECT_ID('Tempdb.dbo.#prdtids') is not null
begin
print 'Exists Table Tempdb.dbo.#prdtids'
end
else
begin
print 'Mot Exists Table Tempdb.dbo.#prdtids'
create table #prdtids(
[prdtid] [char](7) not null
)
end
declare brandidCursor cursor for
select distinct bid from #brandids
open brandidCursor
fetch brandidCursor into @bid
while @@FETCH_STATUS=0
begin
--对这个品牌各个产品小类进行产品筛选开始
declare classidCursor cursor for
select distinct categoryid from #categoryids
open classidCursor
fetch classidCursor into @classid
while @@FETCH_STATUS=0
begin
--对小分类进行筛选开始
IF CURSOR_STATUS('global','prdtidInnerCusor')>=-1
BEGIN
DEALLOCATE prdtidInnerCusor
END
declare prdtidInnerCusor cursor for
select prdtid from FS_MS_Products where ClassID=@classid and brandname=@bid
open prdtidInnerCusor
fetch prdtidInnerCusor into @prdtid
while @@FETCH_STATUS=0
begin
insert into #prdtids (prdtid) values (@prdtid)
fetch next from prdtidInnerCusor into @prdtid
end
close prdtidInnerCusor
deallocate prdtidInnerCusor
--结束
fetch next from classidCursor into @classid
end
close classidCursor
deallocate classidCursor
--筛选结束
fetch next from brandidCursor into @bid
end
close brandidCursor
deallocate brandidCursor
select distinct prdtid from #prdtids