很妖异的一个存储过程中like条件查询问题

[leagiboy]

存储过程如下:,当执行 exec feestulistse '12%' 实际上是有符合条件的记录的,高手帮忙看看,给个意见.

CREATE PROCEDURE FeestulistSE @TJ Char(30) AS
SELECT ChargeMain.ChargeID AS 收费序号, ChargeMain.StuID AS 学号,
students.Stuname AS 姓名, students.stuxx AS 学校, students.stubj AS 班级,
Stulesson.lessonID AS 课程代码, LessonInfo.Lessonname AS 课程名称,
LessonInfo.kefei AS 培训费, LessonInfo.baoming AS 报名费,
LessonInfo.ziliao AS 资料费, LessonInfo.endfee AS 标准收费,
Stulesson.Youhui AS 优惠, ChargeMain.FeeNum AS 收据编号,
ChargeMain.Feedate AS 收费日期,
CASE WHEN ChargeMain.status = 1 THEN '未付清' WHEN ChargeMain.status = 2 THEN
'已付清' ELSE '作废' END AS 状态
FROM ChargeMain INNER JOIN
students ON ChargeMain.StuID = students.StuID INNER JOIN
Stulesson ON ChargeMain.ChargeID = Stulesson.ChargeID INNER JOIN
LessonInfo ON Stulesson.lessonID = LessonInfo.LessonID where Stulesson.lessonID like @tj
GO

[leagiboy]

我最后也发现了这个问题了,谢谢各位!

[fcuandy]

@TJ char(30)
会自动补空格的

like '12%' 实际上执行的是是 like '12% '


你定义为 varchar(30) 就不会有此问题，或者 like rtrim(@tj)

[水族杰纶]

--TRY
CREATE PROCEDURE FeestulistSE @TJ Char(30) AS 
exec('SELECT ChargeMain.ChargeID AS 收费序号, ChargeMain.StuID AS 学号, 
      students.Stuname AS 姓名, students.stuxx AS 学校, students.stubj AS 班级, 
      Stulesson.lessonID AS 课程代码, LessonInfo.Lessonname AS 课程名称, 
      LessonInfo.kefei AS 培训费, LessonInfo.baoming AS 报名费, 
      LessonInfo.ziliao AS 资料费, LessonInfo.endfee AS 标准收费, 
      Stulesson.Youhui AS 优惠, ChargeMain.FeeNum AS 收据编号, 
      ChargeMain.Feedate AS 收费日期, 
      CASE WHEN ChargeMain.status = 1 THEN ''未付清'' WHEN ChargeMain.status = 2 THEN 
      ''已付清'' ELSE ''作废'' END AS 状态 
FROM ChargeMain INNER JOIN 
      students ON ChargeMain.StuID = students.StuID INNER JOIN 
      Stulesson ON ChargeMain.ChargeID = Stulesson.ChargeID INNER JOIN 
      LessonInfo ON Stulesson.lessonID = LessonInfo.LessonID where Stulesson.lessonID like'''+ @tj+'''') 
GO 

[win7cc]

旁听一下

[Liyingyue_FFS]

@TJ Char(30)
应该是char类型后面补了空格，把char换成varchar试一下

[中国风]

@TJ Char(30)

改為nvarchar(30)

條件可改為patindex(@TJ,Stulesson.lessonID)>0