存储过程,在线等

beackoom 2007-05-20 02:47:43
CREATE PROCEDURE dbo.SelectConversations
(
@filter varchar(400),
@sort varchar(400),
@pageNum int,
@pageSize int
)
@filter 表示where限制,@sort 是orger by限制, 要求返回第pageNum页

我的代码如下:

AS

BEGIN
SET NOCOUNT ON

DECLARE @Where varchar(500)
DECLARE @OrderBy varchar(400)
DECLARE @SQL varchar(1000)

SET @Where = 'WHERE Conversation.ConversationTypeID = ConversationType.ConversationTypeID'
+ case when (@filter <> '') then (' AND ' + @filter) end

SET @OrderBy = case when (@sort = '') then '' else ('ORDER BY ' + @sort) end

SET @SQL = '
DECLARE
@ubound INT,
@lbound INT,
@pages INT,
@rows INT,
@pagenumber INT,
@size INT

SET @pagenumber = ' + str(@pageNum) +

' SET @size = ' + str(@pageSize) +

' SELECT @rows = COUNT(*), @pages = COUNT(*) / @size FROM
Conversation, ConversationType ' + @Where +

'IF @rows % ' + str(@pageSize) + '!= 0 SET @pages = @pages + 1
IF ' + str(@pageNum) + ' < 1 SET @pagenumber = 1
IF ' + str(@pageNum) + ' > @pages SET @pagenumber = @pages

SET @ubound = ' + str(@pageSize) + ' * @pagenumber
SET @lbound = @ubound - (' + str(@pageSize) + ' - 1)

SELECT
CurrentPage = @pagenumber,
TotalPages = @pages,
TotalRows = @rows

SELECT
A.ConversationID,
A.ConversationStatusID,
A.ConversationTypeID,
A.InitiationDateTime,
B.Name
FROM
Conversation A,
ConversationType B '
+ @Where +
' GROUP BY
A.ConversationID,
A.ConversationStatusID,
A.ConversationTypeID,
A.InitiationDateTime,
B.Name
HAVING
COUNT(*) BETWEEN @lbound AND @ubound
' + @OrderBy + ' '

Exec(@SQL)
END

但现在问题问题是
// Create IDbCommand
SqlCommand command = new SqlCommand("SelectConversations", connection);
// set its type to CommandType.StoredProcedure
command.CommandType = CommandType.StoredProcedure;

// Add four parameters
.......

// Create new adapter with the IDbCommand
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.SelectCommand = command;
// Create DataSet that will hold results
DataSet dataset = new DataSet();
// Fill data set
adapter.Fill(dataset);

return dataset;

dataset中没有table。 如果在stored procedure中不用Exec(),直接用select是可返回table的,但是直接用select就我没法加上 where 和 order by 参数。

等待高手们提供方法。
...全文
178 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
beackoom 2007-05-20
  • 打赏
  • 举报
回复
找到原因了,
SET @Where = 'WHERE A.ConversationTypeID = B.ConversationTypeID'
+ case when (@filter <> '') then (' AND ' + ltrim(rtrim(@filter))) else '' end
这里一开始没有else。
cbgn 2007-05-20
  • 打赏
  • 举报
回复
这是我现在用的程序,那个test表建出来了呀
你确定下,你拼出来的SQL语句是否是正确的
cbgn 2007-05-20
  • 打赏
  • 举报
回复
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[tj]
@dt2 datetime,
@dt1 datetime,
@strconn varchar(150)
as

declare @cmd varchar(500)

set @cmd = 'SELECT ccode,nname into test FROM '+ @strconn +'tab_operator where snum=''92'' '

exec(@cmd)A


SELECT ccode AS [工号],
(SELECT COUNT(ID) FROM CS_Accept_ReportFix WHERE AcceptMan=ccode AND DateNow <@dt1 AND DateNow>@dt2) AS [记票],
(SELECT COUNT(ID) FROM CS_Accept_Refer WHERE AcceptMan=ccode AND DateNow <@dt1 AND DateNow>@dt2) AS [咨询],
(SELECT COUNT(ID) FROM CS_Accept_Lawsuit WHERE AcceptMan=ccode AND DateNow <@dt1 AND DateNow>@dt2) AS [投诉],
(SELECT COUNT(ID) FROM CS_Accept_Praise WHERE AcceptMan=ccode AND DateNow <@dt1 AND DateNow>@dt2) AS [表扬],
(SELECT COUNT(ID) FROM CS_Accept_Visit WHERE AcceptMan=ccode AND VisitDate <@dt1 AND VisitDate>@dt2) AS [回访],
(SELECT COUNT(ID) FROM CS_Accept_ReportFix WHERE AcceptMan=ccode AND DateNow <@dt1 AND DateNow>@dt2)+
(SELECT COUNT(ID) FROM CS_Accept_Refer WHERE AcceptMan=ccode AND DateNow <@dt1 AND DateNow>@dt2)+
(SELECT COUNT(ID) FROM CS_Accept_Lawsuit WHERE AcceptMan=ccode AND DateNow <@dt1 AND DateNow>@dt2)+
(SELECT COUNT(ID) FROM CS_Accept_Praise WHERE AcceptMan=ccode AND DateNow <@dt1 AND DateNow>@dt2)+
(SELECT COUNT(ID) FROM CS_Accept_Visit WHERE AcceptMan=ccode AND VisitDate <@dt1 AND VisitDate>@dt2) AS [合计]
FROM test ORDER BY ccode

drop table test


beackoom 2007-05-20
  • 打赏
  • 举报
回复
这个是不是由于exec没有真正执行?
beackoom 2007-05-20
  • 打赏
  • 举报
回复
对了,在exec 里面用的select into好像没有创建表,因为外面用select newtable 会有异常
beackoom 2007-05-20
  • 打赏
  • 举报
回复
不把那个表删了,把where条件去掉,也不能取到数
cbgn 2007-05-20
  • 打赏
  • 举报
回复
你看看先不把那个表删了,看看能不能取到数,看看WHERE条件能不能取到数
beackoom 2007-05-20
  • 打赏
  • 举报
回复
好像返回的表中没有rows,难道我select into用错了?
cbgn 2007-05-20
  • 打赏
  • 举报
回复
这样,你在存储过程里,select into 建一个表
然后再select 一下这个表
再把这个表删除了,试试.

111,093

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术 C#
社区管理员
  • C#
  • AIGC Browser
  • by_封爱
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

让您成为最强悍的C#开发者

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