存贮过程的返回参数为什么老返回最后一个select语句的记录总个数

innerfire 2003-08-25 09:13:02
存贮过程如下:

CREATE PROCEDURE ud_SplitUserFromSearch
(@nStartRow int,
@nEndRow int,
@strSql varchar(300),
@nCount int output
)
AS
set nocount on
create table ##t_table
(
[rownum][int] IDENTITY(1,1) Primary Key NOT NULL,
[UserID] [int] NOT NULL ,
[UserType] [int] NOT NULL ,
[Nickname] [varchar] (50) NOT NULL ,
[Sex] [int] NOT NULL ,
[Province] [varchar] (20) NOT NULL ,
[Addr] [varchar] (100) NOT NULL ,
[Postalcode] [varchar] (10) NOT NULL ,
[Birthday] [datetime] NOT NULL ,
[certificateType] [varchar] (50) NOT NULL ,
[CertificateCode] [varchar] (60) NOT NULL ,
[Mail] [varchar] (50) NOT NULL ,
[UserWork] [varchar] (100) NOT NULL ,
[Income] [varchar] (50) NOT NULL ,
[Name] [varchar] (50) NOT NULL ,
[Description] [varchar] (300) NULL ,
[Tele] [varchar] (100) NOT NULL ,
[Password] [varchar] (50) NOT NULL ,
[Portrait] [varchar] (300) NULL ,
[Body] [varchar] (100) NULL ,
[Height] [int] NULL ,
[MarryState] [varchar] (100) NOT NULL ,
[Education] [varchar] (100) NOT NULL ,
[Rate] [varchar] (100) NOT NULL ,
[Purpose] [varchar] (100) NOT NULL ,
[Interesting] [varchar] (300) NULL ,
[Character] [varchar] (300) NULL ,
[Fashion] [varchar] (300) NULL ,
[MobileCode] [varchar] (50) NOT NULL ,
[InputTime] [datetime] NULL ,
[HeadImage] [varchar] (100) NULL ,
[Hot] [int] NULL
)

set RowCount @nEndRow

exec ('insert ##t_table
(
[UserID],
[UserType],
[Nickname],
[Sex],
[Province],
[Addr],
[Postalcode],
[Birthday],
[certificateType],
[CertificateCode],
[Mail] ,
[UserWork],
[Income] ,
[Name] ,
[Description],
[Tele] ,
[Password] ,
[Portrait] ,
[Body] ,
[Height] ,
[MarryState],
[Education],
[Rate],
[Purpose],
[Interesting],
[Character],
[Fashion],
[MobileCode],
[InputTime],
[HeadImage],
[Hot]
)' +@strSql )

select @nCount=count(*) from ##t_table
SELECT * FROM ##t_table WHERE rownum >= @nStartRow and rownum<=@nEndRow
ORDER BY rownum

drop table ##t_table
set nocount off
GO


在VB中用command对象调用该过程

在取command("nCount").value时总是取得是存储过程返回的记录集的记录个数
可我要的是总个数啊,请高手解答!!
...全文
50 3 打赏 收藏 转发到动态 举报
写回复
用AI写文章
3 条回复
切换为时间正序
请发表友善的回复…
发表回复
CrazyFor 2003-08-25
  • 打赏
  • 举报
回复
WHERE rownum >= @nStartRow and rownum<=@nEndRow

where rownum>=1 and rownum<=1 最多一条记录满足条件,没有重复的情况下.


你返回的是多少?
pengdali 2003-08-25
  • 打赏
  • 举报
回复
CREATE PROCEDURE ud_SplitUserFromSearch
(@nStartRow int,
@nEndRow int,
@strSql varchar(300),
@nCount int output
)
AS
set nocount on
create table #t_table
(
[rownum][int] IDENTITY(1,1) Primary Key NOT NULL,
[UserID] [int] NOT NULL ,
[UserType] [int] NOT NULL ,
[Nickname] [varchar] (50) NOT NULL ,
[Sex] [int] NOT NULL ,
[Province] [varchar] (20) NOT NULL ,
[Addr] [varchar] (100) NOT NULL ,
[Postalcode] [varchar] (10) NOT NULL ,
[Birthday] [datetime] NOT NULL ,
[certificateType] [varchar] (50) NOT NULL ,
[CertificateCode] [varchar] (60) NOT NULL ,
[Mail] [varchar] (50) NOT NULL ,
[UserWork] [varchar] (100) NOT NULL ,
[Income] [varchar] (50) NOT NULL ,
[Name] [varchar] (50) NOT NULL ,
[Description] [varchar] (300) NULL ,
[Tele] [varchar] (100) NOT NULL ,
[Password] [varchar] (50) NOT NULL ,
[Portrait] [varchar] (300) NULL ,
[Body] [varchar] (100) NULL ,
[Height] [int] NULL ,
[MarryState] [varchar] (100) NOT NULL ,
[Education] [varchar] (100) NOT NULL ,
[Rate] [varchar] (100) NOT NULL ,
[Purpose] [varchar] (100) NOT NULL ,
[Interesting] [varchar] (300) NULL ,
[Character] [varchar] (300) NULL ,
[Fashion] [varchar] (300) NULL ,
[MobileCode] [varchar] (50) NOT NULL ,
[InputTime] [datetime] NULL ,
[HeadImage] [varchar] (100) NULL ,
[Hot] [int] NULL
)

set RowCount @nEndRow

exec ('insert #t_table
(
[UserID],
[UserType],
[Nickname],
[Sex],
[Province],
[Addr],
[Postalcode],
[Birthday],
[certificateType],
[CertificateCode],
[Mail] ,
[UserWork],
[Income] ,
[Name] ,
[Description],
[Tele] ,
[Password] ,
[Portrait] ,
[Body] ,
[Height] ,
[MarryState],
[Education],
[Rate],
[Purpose],
[Interesting],
[Character],
[Fashion],
[MobileCode],
[InputTime],
[HeadImage],
[Hot]
)' +@strSql )

select @nCount=count(*) from #t_table
set nocount off
SELECT * FROM #t_table WHERE rownum >= @nStartRow and rownum<=@nEndRow ORDER BY rownum

GO
liuyun2003 2003-08-25
  • 打赏
  • 举报
回复
有可能是你的测试数据的问题啊。看看测试数据吧。是不是结果集正好和##TABLE的行数相等。

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧