Question

case when的语句 单引号那里怎么一直出错，创建成功，但是调用的时候总是出现已回复和未回复那里错误，

--分页
create proc getAllQuestion
@pagesize int,
@pageindex int
as
declare @sqlstr nvarchar(1000)
set @sqlstr='select top '
+convert(varchar(10),@pagesize)
+' userID,userName,schoolname,BookName,error,feedbacktime, '
+' case q_state when '+'1'+' then '+'已回复'+' else '+'未回复'+' end as q_state '
+' from JB_BookOpinion jb left join QuestionAns qu on jb.userid=qu.q_userid where userID not in'
+'(select top '
+convert(varchar(10),(@pageindex-1)*@pagesize)
+' userID from JB_BookOpinion)'
exec sp_executesql @sqlstr

Answer 1

create proc getAllQuestion
@pagesize int,
@pageindex int
as
declare @sqlstr nvarchar(1000)
set @sqlstr='select top '
+convert(varchar(10),@pagesize)
+' userID,userName,schoolname,BookName,error,feedbacktime, '
+' case q_state when '+'1'+' then '+''''+'已回复+''''+'+' else '+'+''''+未回复+''''+'+' end as q_state '
+' from JB_BookOpinion jb left join QuestionAns qu on jb.userid=qu.q_userid where userID not in'
+'(select top '
+convert(varchar(10),(@pageindex-1)*@pagesize)
+' userID from JB_BookOpinion)'
exec sp_executesql @sqlstr
'已回复'+' else '+'未回复' 这个要改成 ''''+'已回复+''''+'+' else '+'+''''+未回复+''''
因为已回复和未回复是字符串，要用单引号括起来，而拼接字符串时候，要加一个单引号就要用四个单引号。

Answer 2

要这样写
create proc getAllQuestion
@pagesize int,
@pageindex int
as
declare @sqlstr nvarchar(1000)
set @sqlstr='select top '
+convert(varchar(10),@pagesize)
+' userID,userName,schoolname,BookName,error,feedbacktime, '
+' case q_state when '+'1'+' then '+'''已回复'''+' else '+'''未回复'''+' end as q_state '
+' from JB_BookOpinion jb left join QuestionAns qu on jb.userid=qu.q_userid where userID not in'
+'(select top '
+convert(varchar(10),(@pageindex-1)*@pagesize)
+' userID from JB_BookOpinion)'
exec sp_executesql @sqlstr

Answer 3

一个常量,且能与Switch语句中的变量类型匹配就行.