存储过程返回记录总为空，如果修改：

[seafarer777]

CREATE PROCEDURE txl_list_user
(
@userid int,
@groupid int,
@StartNum int,
@LeaveNum int,
@prevtype int,
@keyword varchar
)
AS
declare @StrSql varchar(1000)
if @prevtype=0
begin
set @StrSql="select id,realname from (select top "+str(@StartNum)+" id,realname from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and status=0 order by id desc) a where a.id not in (select top "+str(@LeaveNum)+" ID from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and status=0 order by id desc)"
end
if @prevtype=1
begin
set @strSql="select id,realname from (select top "+str(@StartNum)+" id,realname from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and (charindex('+@keyword+',realname)>0 or charindex('+@keyword+',pinyin)>0) and status=0 order by id desc) a where a.id not in (select top "+str(@LeaveNum)+" ID from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and (charindex('+@keyword+',realname)>0 or charindex('+@keyword+',pinyin)>0) and status=0 order by id desc)"
end
exec(@strSql)
GO

[seafarer777]

if @prevtype=0
这个条件执行的语句没问题，就是@prevtype=1 时得到的结果总为空

[zlp321002]

--@keyword varchar 要定义长度

[vivianfdlpw]

@keyword varchar

================>

@keyword varchar(20)

[zlp321002]

CREATE PROCEDURE txl_list_user
(
@userid int,
@groupid int,
@StartNum int,
@LeaveNum int,
@prevtype int,
@keyword varchar(100) --定义长度
)
AS
declare @StrSql varchar(1000)
if @prevtype=0
begin
set @StrSql="select id,realname from (select top "+str(@StartNum)+" id,realname from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and status=0 order by id desc) a where a.id not in (select top "+str(@LeaveNum)+" ID from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and status=0 order by id desc)"
end
if @prevtype=1
begin
set @strSql="select id,realname from (select top "+str(@StartNum)+" id,realname from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and (charindex('+@keyword+',realname)>0 or charindex('+@keyword+',pinyin)>0) and status=0 order by id desc) a where a.id not in (select top "+str(@LeaveNum)+" ID from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and (charindex('+@keyword+',realname)>0 or charindex('+@keyword+',pinyin)>0) and status=0 order by id desc)"
end
exec(@strSql)
GO

[vivianfdlpw]

这个问题蛮多的嘛

[paoluo]

你的存储过程能通过语法检查吗？？

SQL没有"的写法吧，只有'。

[phantomMan]

varchar 默认长度为1:

declare @keyword varchar
select @keyword='afaf'
select @keyword


----
a

（所影响的行数为 1 行）

[子陌红尘]

单看存储过程似乎没什么问题，一旦@StartNum <= @LeaveNum，返回的结果集将肯定为空；
另外，一旦@prevtype 不等于 0 且不等于 1，存储过程将不返回结果集。

[seafarer777]

执行@prevtype=1条件时还是得不到相应的结果

[vivianfdlpw]

把你的sql语句print出来然后在查询分析器里调试

[seafarer777]

@StartNum <= @LeaveNum 这种情况不会发生的,程序里有控制的，而且我是在查询分析器调试存储过程的，@StartNum ,@LeaveNum 两个参数我已经指定值了

[seafarer777]

打印出来的语句是这样的
select id,realname from (select top 2 id,realname from txl_userinfo where userid=10000 and groupid=1002 and (charindex('+@keyword+',realname)>0 or charindex('+@keyword+',pinyin)>0) and status=0 order by id desc) a where a.id not in (select top 0 ID from txl_userinfo where userid=10000 and groupid=1002 and (charindex('+@keyword+',realname)>0 or charindex('+@keyword+',pinyin)>0) and status=0 order by id desc)

@keyword没有得到值，这是怎么回事？

[seafarer777]

可以了
set @strSql="select id,realname from (select top "+str(@StartNum)+" id,realname from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and (charindex('"+@keyword+"',realname)>0 or charindex('"+@keyword+"',pinyin)>0) and status=0 order by id desc) a where a.id not in (select top "+str(@LeaveNum)+" ID from txl_userinfo where userid="+str(@userid)+" and groupid="+str(@groupid)+" and (charindex('"+@keyword+"',realname)>0 or charindex('"+@keyword+"',pinyin)>0) and status=0 order by id desc)"