两个综合查询的SQL存储过程！

在作综合查询时用到的，拿出来共享下 
第一种：

 程序代码

Alter PROCEDURE SearchSiteInfo
(
@SiteName nvarchar(50),
@SiteDoMain nvarchar(50),
@SiteCNDoMain nvarchar(50)
)
AS
declare @tSiteName nvarchar(500)
declare @tSiteDoMain nvarchar(500)
declare @tSiteCNDoMain nvarchar(500)

if(@SiteName<>'')
begin
set @tSiteName=' and SiteName like ''%'+@SiteName+'%'''
end
if(@SiteDoMain<>'')
begin
set @tSiteDoMain=' and SiteDoMain like ''%'+@SiteDoMain+'%'''
end
if(@SiteCNDoMain<>'')
begin
set @tSiteCNDoMain=' and SiteCNDoMain like ''%'+@SiteCNDoMain+'%'''
end

exec('select
    [ID],
    [SiteName],
    [SiteName2],
    [SiteName3],
    [SiteCNDoMain],
    [SiteDoMain],
    [SiteDoMain2],
    [SiteDoMain3],
    [OperationName],
    [OperationPhone],
    [OperaEmail],
    [SiteWorkAddress],
    [SiteZipCode],
    [SiteFax],
    [CompanyName],
    [BusinessRoom],
    [BusinessRoomAddress]
    
FROM
    SiteRecords
Where 1=1 '+@tSiteName+@tSiteDoMain+@tSiteCNDoMain)

第二种：

 程序代码

Alter PROCEDURE SearchSiteInfo(
@SiteName     nvarchar(50),
@SiteDoMain   nvarchar(50),
@SiteCNDoMain nvarchar(50))
AS
BEGIN
    DECLARE @SQL NVARCHAR(4000)
    SET @SQL=N'select
                   [ID],
                   [SiteName],
                   [SiteName2],
                   [SiteName3],
                   [SiteCNDoMain],
                   [SiteDoMain],
                   [SiteDoMain2],
                   [SiteDoMain3],
                   [OperationName],
                   [OperationPhone],
                   [OperaEmail],
                   [SiteWorkAddress],
                   [SiteZipCode],
                   [SiteFax],
                   [CompanyName],
                   [BusinessRoom],
                   [BusinessRoomAddress]
               FROM
                  SiteRecords
               Where 1=1'
    
    if(@SiteName='')
        SET @SQL=@SQL+N' and SiteName like ''%'+@SiteName+N'%'''

    if(@SiteDoMain='')
        SET @SQL=@SQL+N' and SiteDoMain like ''%'+@SiteDoMain+N'%'''

    if(@SiteCNDoMain='')
        SET @SQL=@SQL+N' and SiteCNDoMain like ''%'+@SiteCNDoMain+N'%'''
    
    EXEC(@SQL)
END
GO