alter PROCEDURE dbo.YourCompany_ywkj_TopicList
@InProductID nvarchar(10),
@EditionID nvarchar(10),
@TypeID nvarchar(10),
@UserID nvarchar(10),
@ALL nvarchar(10),
@startIndex int,
@endIndex int
AS
declare @AllSql nvarchar(2000),@sql varchar(1000),@str nvarchar(200)
set @str=''
set @sql = N'select m.TopicID,
m.ProductID,
CONVERT(varchar(12),m.CreatedDate,111) as CreatedDate,
m.Title,
t.Typename,p.ProductName,
m.EditionID,
(select ProductName from Product where EditionID=ProductID)as Edition,
u.username
from Topic m,Type t,Product p,Users u
where m.typeid = t.typeid and p.ProductID = m.ProductID and m.CreatedByUser = u.userID'
IF @ALL is not null
begin
set @str = ' and 1=1 '
set @AllSql = @sql+ ' and 1=1 '
end
IF @UserID is not NULL
BEGIN
set @str =@str+ ' and m.CreatedByUser='+@UserID+' '
set @AllSql = @sql+' and m.CreatedByUser='+@UserID+' '
END
IF @TypeID is not null
begin
set @str =@str+ ' and m.typeid='+@TypeID+' '
set @AllSql = @sql+ ' and m.typeid='+@TypeID+' '
end
IF @EditionID is not NULL
begin
set @str =@str+' and m.EditionID='+@EditionID+' '
set @AllSql = @sql+' and m.EditionID='+@EditionID+' '
end
IF @InProductID is not null
begin
set @str =@str+ ' and m.ProductID in (select ProductID from dbo.FUNC_GET_Edition(1,'+@InProductID+',0)) '
set @AllSql = @sql+' and m.ProductID in (select ProductID from dbo.FUNC_GET_Edition(1,'+@InProductID+',0)) '
end
declare @PageSql nvarchar(2000)
set nocount on
set rowcount @endIndex
set @PageSql = 'declare @indextable table(id int identity(1,1),nid int) insert into @indextable(nid) select TopicID from Topic m where 1=1' + @str
print @PageSql
EXECUTE sp_executesql @PageSql
print @AllSql
set @AllSql =@AllSql+ 'and m.TopicID=t.nid
and t.id between '+@startIndex+' and '+@endIndex+' order by t.id'
set nocount off
print @AllSql
EXECUTE sp_executesql @AllSql
GO
