搜索连接字符串存储过程【原创】

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)

转载于:https://www.cnblogs.com/tiantangwater/archive/2008/12/24/1361706.html