多条件查询的程序

//设置查询语句
string strSql = "SELECT * FROM [user] where UserState=1 ";
//如果用户名不为空则添加查询条件
if (UserName!="")
{
    strSql += "and (UserName'= "+UserName+"') ";
}
//如果性别不为空则添加查询条件
if (Sex!="")
{
    strSql += "and (Sex'= "+Sex+"') ";
}

CREATE PROCEDURE [dbo].[UserCheck]
@UserId varchar(50) = null,
@UserName varchar(20) = null,
@RealName varchar(20) = null,
@Sex bit = null,
@JobTitle varchar(50) = null,
@Organ varchar(50) = null,
@IDCardType smallint = null,
@IDCard varchar(50) = null,
@Mobile varchar(50) = null
AS
BEGIN
select * from [user]
where UserId like case when @UserId is null then UserId else @UserId end
and UserName like case when @UserName is null then UserName else @UserName end
and RealName like case when @RealName is null then RealName else @RealName end
and Sex = case when @Sex is null then Sex else @Sex end
and JobTitle like case when @JobTitle is null then JobTitle else @JobTitle end
and Organ like case when @Organ is null then Organ else @Organ end
and IDCardType = case when @IDCardType is null then IDCardType else @IDCardType end
and IDCard like case when @IDCard is null then IDCard else @IDCard end
and Mobile like case when @Mobile is null then Mobile else @Mobile end
END