1、CREATE PROCEDURE dbo.SearchUserInfo
(@Age int )
AS
SELECT * FROM UserInfo WHERE Age like '%'+cast(@Age as nvarchar(50))+'%'
RETURN
2、
CREATE PROCEDURE dbo.Mfx_SearchUserInfo
--用户搜索[state-审核状态(2为全部)、UserLever-用户级别(0为全部)、keyWord(关键字)]
@state int,
@UserLever int,
@keyWord nvarchar(50)
AS
begin
declare @sql nvarchar(4000)
set @sql=' select *,(select nvLeverName from UserLever where UserInfo.iUserLeverID=UserLever.id) as nvLeverName from UserInfo where (1=1) '
if(@state=2)
begin
if(@UserLever=0)
begin
set @sql=@sql
end
else
begin
set @sql=@sql+' and iUserLeverID='+char(39)+cast(@UserLever as nvarchar(50))+char(39)
end
end
else
begin
if(@UserLever=0)
begin
set @sql=@sql+' and iStateID='+char(39)+cast(@state as nvarchar(50)) +char(39)
end
else
begin
set @sql=@sql+' and iStateID='+char(39)+cast(@state as nvarchar(50))+char(39) +' and iUserLeverID='+char(39)+cast(@UserLever as nvarchar(50))+char(39)
end
end
end
set @sql=@sql+'AND( nvName like'+char(39)+'%'+@keyWord+'%'+char(39)+'or nvUserName like'+char(39)+'%'+@keyWord+'%'+char(39)+'or nvPhone like'+char(39)+'%'+@keyWord+'%'+char(39)+'or nvIdentityCard like'+char(39)+'%'+@keyWord+'%'+char(39)+')'+'order by UserInfo.ID desc '
exec(@sql)