Discuz.Web/install/sqlscript/sqlserver/album/setup2.2.sql

这里只显示每个文件的开头 60 行。登录后可以解锁完整代码。

微信扫码关注公众号，代码整理好后第一时间通知你。

CREATE  PROCEDURE [dnt_createphototags]
@tags nvarchar(55),
@photoid int,
@userid int,
@postdatetime datetime
AS
BEGIN
	exec [dnt_createtags] @tags, @userid, @postdatetime

	UPDATE [dnt_tags] SET [pcount]=[pcount]+1,[count]=[count]+1
	WHERE EXISTS (SELECT [item] FROM [dnt_split](@tags, ' ') AS [newtags] WHERE [newtags].[item] = [tagname])
	
	INSERT INTO [dnt_phototags] (tagid, photoid)
	SELECT tagid, @photoid FROM [dnt_tags] WHERE EXISTS (SELECT * FROM [dnt_split](@tags, ' ') WHERE [item] = [dnt_tags].[tagname])
END
GO

CREATE PROCEDURE [dnt_deletephototags]
	@photoid int
 AS
BEGIN       
	UPDATE [dnt_tags] SET [count]=[count]-1,[fcount]=[fcount]-1 
	WHERE EXISTS (SELECT [tagid] FROM [dnt_phototags] WHERE [photoid] = @photoid AND [tagid] = [dnt_tags].[tagid])

    DELETE FROM [dnt_phototags] WHERE [photoid] = @photoid	
END
GO

CREATE PROCEDURE [dnt_getgoodslistByCid]
@categoryid int,
@pagesize int,
@pageindex int,
@condition varchar(500),
@orderby varchar(100),
@ascdesc int
AS

DECLARE @strSQL varchar(5000)
DECLARE @sorttype varchar(5)

IF @ascdesc=0
   SET @sorttype='ASC'
ELSE
   SET @sorttype='DESC'

IF @pageindex <= 1
	SET @strSQL = 'SELECT TOP '+STR(@pagesize)+' * FROM [dnt_goods] WHERE ([categoryid] = '+STR(@categoryid)+' OR CHARINDEX('',''+CAST('+STR(@categoryid)+' AS VARCHAR(10))+'','' , '',''+RTRIM([parentcategorylist])+'','')>0)  '+ @condition +' ORDER BY '+@orderby+' '+@sorttype
ELSE
	IF @sorttype = 'DESC'
		SET @strSQL = 'SELECT TOP '+STR(@pagesize)+' * FROM [dnt_goods] WHERE [goodsid] < (SELECT MIN([goodsid])  FROM (SELECT TOP '+ STR((@pageindex - 1) * @pagesize) + ' [goodsid] FROM [dnt_goods]  WHERE  ([categoryid] = '+STR(@categoryid)+' OR CHARINDEX('',''+CAST('+STR(@categoryid)+' AS VARCHAR(10))+'','' , '',''+RTRIM([parentcategorylist])+'','')>0)  '+ @condition +' ORDER BY '+@orderby+' '+@sorttype+') AS tblTmp ) AND ([categoryid] = '+STR(@categoryid)+' OR CHARINDEX('',''+CAST('+STR(@categoryid)+' AS VARCHAR(10))+'','' , '',''+RTRIM([parentcategorylist])+'','')>0) '+@condition+' ORDER BY '+@orderby+' '+@sorttype
	ELSE
		SET @strSQL = 'SELECT TOP '+STR(@pagesize)+' * FROM [dnt_goods] WHERE [goodsid] > (SELECT MAX([goodsid])  FROM (SELECT TOP '+ STR((@pageindex - 1) * @pagesize) + ' [goodsid] FROM [dnt_goods]  WHERE  ([categoryid] = '+STR(@categoryid)+' OR CHARINDEX('',''+CAST('+STR(@categoryid)+' AS VARCHAR(10))+'','' , '',''+RTRIM([parentcategorylist])+'','')>0) '+ @condition +' ORDER BY '+@orderby+' '+@sorttype+') AS tblTmp ) AND ([categoryid] = '+STR(@categoryid)+' OR CHARINDEX('',''+CAST('+STR(@categoryid)+' AS VARCHAR(10))+'','' , '',''+RTRIM([parentcategorylist])+'','')>0) '+@condition+' ORDER BY '+@orderby+' '+@sorttype	
EXEC(@strSQL)
GO


CREATE PROCEDURE dnt_getphotolist
@albumid int,
@pagesize int,
@pageindex int