多条件查询SQL,存储过程版,不用拼接SQL

开发中有很多查询时多条件的,通常的做法是,拼接字符串,但是这种做法很容易被黑客攻击,所以,保险点的做法是将参数同过SQLPARAMETE传递到存储过程中

 

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

posted @ 2011-01-24 16:31  西门啥都吹  阅读(1683)  评论(4编辑  收藏  举报