CREATE PROCEDURE SearchCatalog
(
@PageNumber TINYINT,/*当前第几页*/
@ProductsPerPage TINYINT, /*显示多页条信息*/
@HowManyResults SMALLINT OUTPUT, /*总信息数*/
@ClassId TINYINT,/*类的ID*/
@endstr nvarchar(100)
AS
declare
@SQL Varchar(4000),
@SQL1 Varchar(4000)
DECLARE
@MypdView TABLE
(RowNumber SMALLINT IDENTITY (1,1) NOT NULL,
id int,
showname VARCHAR(1000),
price VARCHAR(100),
keywords varchar(500),
content varchar(1000),
hits varchar(10),
corpname varchar(500),
Vip bit
)

set @SQL='INSERT INTO @MypdView
select M.ID,M.ShowName,M.PriceTerms,M.Keywords,M.Content,M.hits,S.Corp_name,vip=1 from dbo.Sys_Corporation as S,dbo.MYPD_Information as M where M.UserId=S.id and vip=1 '+@endstr+' ORDER BY M.id DESC'
exec(@SQL)
set @SQL1='INSERT INTO @MypdView
select M.ID,M.ShowName,M.PriceTerms,M.Keywords,M.Content,M.hits,S.Corp_name,vip=0 from dbo.Sys_Corporation as S,dbo.MYPD_Information as M where M.UserId=S.id and vip<>1 '+@endstr+' ORDER BY M.id DESC'
exec(@SQL1)

SELECT @HowManyResults = COUNT(*)

FROM @MypdView

SELECT id,showname,price,keywords,content,hits,corpname,vip

FROM @MypdView

WHERE

RowNumber BETWEEN (@PageNumber-1) * @ProductsPerPage + 1

AND @PageNumber * @ProductsPerPage ORDER BY RowNumber DESC
GO

错误：
服务器: 消息 156，级别 15，状态 1，过程 SearchCatalog，行 8
在关键字 'AS' 附近有语法错误。

[此贴子已经被作者于2007-3-30 15:30:58编辑过]