CREATE PROCEDURE news_list
-- Add the parameters for the stored procedure here
@pagesize int,
@type nvarchar(100),
@trade nvarchar(100),
@key nvarchar(max),
@pagecount int output,
@itemcount int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
declare @str_ds nvarchar(max)
declare @str_item nvarchar(max)
if @type != ''
begin
set @str_ds='select top ' + @pagesize + ' n.ID,titles,times from news as n,news_type as nt where 1=1 ' + @type + @trade + @key + ' order by times desc,ID desc '
set @str_item='select count(n.ID) from news as n,news_type as nt where 1=1 ' + @type + @trade + @key
end
else
begin
set @str_ds='select top ' + @pagesize + ' ID,titles,times from news where 1=1 ' + @trade + @key + ' order by times desc,ID desc '
set @str_item='select count(ID) from news where 1=1 ' + @trade + @key
end
exec @str_ds
exec @str_ds
select @itemcount=exec @str_item
set @pagecount=(@itemcount - 1) / @pagesize + 1
END
GO
select @itemcount=exec @str_item 这句报错,关键字 'exec' 附近有语法错误。求教如何解决
exec news_list '','','','','',@itemcount output
select @itemcount
-- Add the parameters for the stored procedure here
@pagesize int,
@type nvarchar(100),
@trade nvarchar(100),
@key nvarchar(max),
@pagecount int output,
@itemcount int output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
declare @str_ds nvarchar(max)
declare @str_item nvarchar(max)
if @type != ''
begin
set @str_ds='select top ' + @pagesize + ' n.ID,titles,times from news as n,news_type as nt where 1=1 ' + @type + @trade + @key + ' order by times desc,ID desc '
set @str_item='select @r = count(n.ID) from news as n,news_type as nt where 1=1 ' + @type + @trade + @key
end
else
begin
set @str_ds='select top ' + @pagesize + ' ID,titles,times from news where 1=1 ' + @trade + @key + ' order by times desc,ID desc '
set @str_item='select @r = count(ID) from news where 1=1 ' + @trade + @key
end
exec (@str_ds) exec sp_executesql @str_item,N'@r int output',@itemcount output set @pagecount=(@itemcount - 1) / @pagesize + 1
END
GO
Haiwer,你的偶用过了,数据是取出来了,但是为何@itemcount的数值是实际数值的4倍??
汗偶数据库中一共17条数据,@itemcount返回的是68条数据求解O(∩_∩)O~