一条存储过程和一条sql语句，条件都一样，可为什么影响的数目不同相呢？

CREATE   PROCEDURE sp_select_tab
    @tab_name varchar(150),
    @fields varchar(150),
    @field varchar(50),
    @keyword varchar(100),
    @u_info_id varchar(250),
   @d_flag int
as
    if(@d_flag=1)
        exec('select '+ @fields+'  from '+ @tab_name+'  where ' +@field+'<'+@keyword+'and id in (' +@u_info_id+ ')  order by id asc')
else
       exec('select '+ @fields+'  from '+ @tab_name+'  where '+ @field+'<'+@keyword +'and id in (' +@u_info_id+ ') order by id desc ')
GO

以上是存储过程；

exec sp_select_tab @fields='id,info_mode,title,save_time,end_time',@tab_name='supply_info',@field='end_time',@u_info_id='165,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265',@keyword='"2004-8-27"',@d_flag=1

以上是调用存储过程；

select  count(*) as counter from supply_info where end_time<'2004-8-27' and  id in(165,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265)

以上是sql语句。

id in(这里是相同的)

可存储过程查出的有60条记录。
可sql统计的却有90条记录。
这是怎么一回事呢？

15 个解决方案

#1

pass by.

#2

等了N久，却等来个pass by.
郁闷。。。
高手安在。。

#3

@keyword='"2004-8-27"'
应为:@keyword='''2004-8-27'''
注意是三个单引号,试试

#4

to gaodongsheng(东升):
那样子是不行的，我试过了。

#5

存储过程实际执行的语句
select id,info_mode,title,save_time,end_time from supply_info where end_time<"2004-8-27"and id in (165,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,22) order by id asc
sql语句的执行
select count(*) as counter from supply_info where end_time<'2004-8-27' and id in(165,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265)

#6

还是记录的个数不对吗?
还是报错啊?

#7

该不是单引号用的是全角的吧
你把拼成的SQL语句输出来看看,分析一下.

#8

看出你的问题了吗？
是因为你的,@u_info_id字段设小了，
字符串被截断了，把字段类型设为varchar(8000)看一下吧

#9

呵呵：）

#10

to zjmym(缘木):
是这样的，可为什么影响的数目不对呢？

#11

to gaodongsheng(东升):
和原来的结果一样；
没有用到什么全角。

#12

截断以后id in 条件中的条件数就少了很多，所以导致有些数据查不出来

#13

问题解决了没有？如果解决了我就不再回复了

#14

to zjmym(缘木):
OK了，
是@u_info_id字段设小了，
犯了个低级的错误!

谢了两位，结贴了。。

#15

呵呵，不客气，谁都会犯错误

#1