Copy Code code as follows:
DECLARE @PAGESIZE INT
DECLARE @PAGEINDEX INT
DECLARE @PAGECOUNT INT
DECLARE @RECORDCOUNT INT
SELECT @PAGESIZE =5
SELECT @PAGEINDEX =1
DECLARE @FIELDNAME VARCHAR (50)
DECLARE @FIELDVALUE VARCHAR (50)
DECLARE @OPERATION VARCHAR (50)
--Combination conditions
DECLARE @WHERE NVARCHAR (1000)
SELECT @WHERE = ' WHERE notdisplay=0 '
DECLARE ABC CURSOR for
SELECT fieldname,fieldvalue,operation from Tbparameters
OPEN ABC
FETCH NEXT from ABC into @FIELDNAME, @FIELDVALUE, @OPERATION
While @ @FETCH_STATUS =0
BEGIN
IF (@OPERATION = ' like ')
SELECT @WHERE = @WHERE + ' and ' + @FIELDNAME + ' like '% ' + @FIELDVALUE + '% '
ELSE
BEGIN
IF (@FIELDNAME = ' CLASSID ')
BEGIN
DECLARE @ROOTID INT
SELECT @ROOTID = @FIELDVALUE
--Adding a subclass of a value of a specified category to a temporary table
INSERT into Tbtemclass (ID) SELECT IDs from Tbsdinfoclass WHERE rootid= @ROOTID
--use Grand to put the smallest category of the specified category into the temporary table
DECLARE CLASSID CURSOR for
SELECT ID from Tbtemclass
OPEN CLASSID
FETCH NEXT from CLASSID into @ROOTID
While @ @FETCH_STATUS =0
BEGIN
--If a subclass is judged, the subclass is added to the temporary table, and the category is deleted so that the cursor loops in the temporary table
IF (EXISTS (SELECT ID from Tbsdinfoclass WHERE rootid= @ROOTID))
BEGIN
INSERT into Tbtemclass (ID) SELECT IDs from Tbsdinfoclass WHERE rootid= @ROOTID
DELETE from Tbtemclass WHERE id= @ROOTID
End
FETCH NEXT from CLASSID into @ROOTID
End
Close CLASSID
Deallocate CLASSID
--adding itself to the temporary table
INSERT into Tbtemclass (ID) SELECT @FIELDVALUE
Select @WHERE = @WHERE + ' and CLASSID in (select ID from Tbtemclass) '
End
ELSE
SELECT @WHERE = @WHERE + ' and ' + @FIELDNAME + @OPERATION + @FIELDVALUE
End
FETCH NEXT from ABC into @FIELDNAME, @FIELDVALUE, @OPERATION
End
Close ABC
Deallocate ABC
TRUNCATE TABLE Tbparameters
----Count statement
DECLARE @COUNTSQL NVARCHAR (500)
Select @COUNTSQL =n ' Select @RECORDCOUNT =count (*) from Tbsdinfo INNER JOIN tbuser on Tbsdinfo. Username=tbuser. USERNAME '
SELECT @COUNTSQL = @COUNTSQL + @WHERE
--
----Execution statistics
EXEC sp_executesql @COUNTSQL,
N ' @RECORDCOUNT INT out ',
@RECORDCOUNT out
--
----Count pages
SET @PageCount = CEILING (@RecordCount * 1.0/@PageSize)
--
----Query statement
DECLARE @SQL NVARCHAR (2000)
DECLARE @ORDERBY VARCHAR (100)
SELECT @ORDERBY = ' ORDER by Tbsdinfo. Iscommon desc,tbsdinfo.commontime Desc,tbsdinfo. Createtime DESC '
IF (@PAGEINDEX =1)
BEGIN
SELECT @SQL = ' INSERT into Tbteminfo (id,title,remark,createtime,endtime,webdomain,classid,classname,typeid,typename , Provinceid,province,cityid,city,companyname,address,usertype) '
Select @SQL = @SQL + ' select Top ' +convert (VARCHAR (4), @PAGESIZE) + ' Tbsdinfo.id,title,remark,tbsdinfo. Createtime,endtime, Webdomain,tbsdinfo. CLASSID, (SELECT CLASSNAME from Tbsdinfoclass WHERE tbsdinfoclass.id=tbsdinfo. CLASSID) as Classname,typeid, (SELECT TYPENAME from Tbsdinfotype WHERE tbsdinfo. Typeid=tbsdinfotype.id) as Typename,provinceid, (SELECT Province from Tbprovince WHERE Tbprovince.id=provinceid) as Province,cityid, (SELECT city from Tbcity WHERE Tbcity.id=cityid) as City,companyname,tbsdinfo. Address,usertype from Tbsdinfo INNER JOIN tbuser on Tbuser. Username=tbsdinfo. USERNAME '
SELECT @SQL = @SQL + @WHERE
SELECT @SQL = @SQL + @ORDERBY
End
ELSE
BEGIN
DECLARE @MINRECORD INT
SELECT @MINRECORD = (@PAGEINDEX-1) * @PAGESIZE
SELECT @SQL = ' INSERT into Tbteminfo (id,title,remark,createtime,endtime,webdomain,classid,classname,typeid,typename , Provinceid,province,cityid,city,companyname,address,usertype) '
Select @SQL = @SQL + ' select Top ' +convert (VARCHAR (4), @PAGESIZE) + ' tbsdinfo.id, Title,remark,tbsdinfo. Createtime,endtime, Webdomain,tbsdinfo. CLASSID, (SELECT CLASSNAME from Tbsdinfoclass WHERE tbsdinfoclass.id=tbsdinfo. CLASSID) as Classname,typeid, (SELECT TYPENAME from Tbsdinfotype WHERE tbsdinfo. Typeid=tbsdinfotype.id) as Typename,provinceid, (SELECT Province from Tbprovince WHERE Tbprovince.id=provinceid) as Province,cityid, (SELECT city from Tbcity WHERE Tbcity.id=cityid) as City,companyname,tbsdinfo. Address,usertype from Tbsdinfo INNER JOIN tbuser on Tbuser. Username=tbsdinfo. USERNAME '
IF (@WHERE <> ")
SELECT @SQL = @SQL + @WHERE + ' and '
ELSE
SELECT @SQL = @SQL + ' WHERE '
Select @SQL = @SQL + ' tbsdinfo.id not in (select Top ' +convert (VARCHAR (4), @MINRECORD) + ' tbsdinfo.id from Tbsdinfo INNER JOIN T Buser on Tbuser. Username=tbsdinfo. USERNAME ' + @WHERE + @ORDERBY + ') '
SELECT @SQL = @SQL + @ORDERBY
End
--print @SQL
--Execute Query
--the result of the query is to place the found record in a temporary table and then query out the corresponding parent class and Root class record by the downstream standard
EXEC (@SQL)
DECLARE @CLASSID INT
DECLARE @ID INT
DECLARE TEM CURSOR for
SELECT Id,classid from Tbteminfo
OPEN TEM
FETCH NEXT from the TEM into @ID, @CLASSID
While @ @FETCH_STATUS =0
BEGIN
DECLARE @NS VARCHAR (500)
DECLARE @DS VARCHAR (200)
SELECT @NS = '
SELECT @DS = '
DECLARE @TEMROOTID INT
DECLARE @TEMTS VARCHAR (50)
SELECT @CLASSID =id, @TEMTS =classname, @TEMROOTID =rootid from Tbsdinfoclass WHERE id= @CLASSID
SELECT @NS = @TEMTS + ' # ' + @NS
SELECT @DS =convert (VARCHAR), @CLASSID) + ' # ' + @DS
while (@TEMROOTID >0)
BEGIN
SELECT @TEMROOTID =rootid, @CLASSID =id, @TEMTS =classname from Tbsdinfoclass WHERE id= @TEMROOTID
SELECT @NS = @TEMTS + ' # ' + @NS
SELECT @DS =convert (VARCHAR), @CLASSID) + ' # ' + @DS
End
UPDATE tbteminfo SET ns= @NS, ds= @DS WHERE id= @ID
FETCH NEXT from the TEM into @ID, @CLASSID
End
Close TEM
Deallocate TEM
SELECT * from Tbteminfo
TRUNCATE TABLE Tbteminfo
TRUNCATE TABLE Tbtemclass
