求 高人指教 存储过程字符串连接的方法

[jaying]

use gshop
go
create proc sp_getMessageList
@userID int,
@readStatus tinyint --0,1,2对应(未读，已读，所有)
as


declare @strsql varchar(50)

if @readStatus=0
begin
set @strsql = ' and readStatus=0'
end
else if @readStatus=1
begin
set @strsql = ' and readStatus=1'
end
else
begin
set @strsql = ' '
end
select messageID,inceptUserID,inceptUserName,messageTopic,messageInfo,sendUserName,sendTime,readTime,readStatus
from Tb_Message
where inceptUserID=@userID and parentID=0 + @strsql


exec exec sp_getMessageList 1,2 没错
exec exec sp_getMessageList 1,0
将 varchar 值 ' and readStatus=0' 转换为数据类型为 int 的列时发生语法错误。
请高人指教怎么存储过程中怎么连接字符串

[Unending]

这样做0和1都是运行不了的
还是老老实实的写,运行数度反而会快一点
use gshop
go
create proc sp_getMessageList
@userID int,
@readStatus tinyint --0,1,2对应(未读，已读，所有)
as


declare @strsql varchar(50)

if @readStatus=0
begin
select messageID,inceptUserID,inceptUserName,messageTopic,messageInfo,sendUserName,sendTime,readTime,readStatus
from Tb_Message
where inceptUserID=@userID and parentID=0 and readStatus=0
end
else if @readStatus=1
begin
select messageID,inceptUserID,inceptUserName,messageTopic,messageInfo,sendUserName,sendTime,readTime,readStatus
from Tb_Message
where inceptUserID=@userID and parentID=0 and readStatus=1
end
else
begin
select messageID,inceptUserID,inceptUserName,messageTopic,messageInfo,sendUserName,sendTime,readTime,readStatus
from Tb_Message
where inceptUserID=@userID and parentID=0
end

当然也可以把前面两种情况合起来

[freelyl]

试试
use gshop
go
create proc sp_getMessageList
@userID int,
@readStatus tinyint --0,1,2对应(未读，已读，所有)
as


declare @strsql varchar(50)

if @readStatus=0
begin
set @strsql = ' and readStatus=0'
end
else if @readStatus=1
begin
set @strsql = ' and readStatus=1'
end
else
begin
set @strsql = ' '
end
exec 'select messageID,inceptUserID,inceptUserName,messageTopic,messageInfo,sendUserName,sendTime,readTime,readStatus
from Tb_Message
where inceptUserID=@userID and parentID=0' + @strsql