--再試試一下我改過後的:
declare @sql varchar(400)
set @sql = 'select eafi_addresslist. *, case sex when 0 then ''男'' else ''女'' end as sexname from eafi_addresslist '
if(@name <> '')
set @sql = @sql + ' where ( name like ''%' + @name + '%'') '
if(@nickname <> '')
set @sql = @sql + ' where (nickname like ''%' + @nickname + '%'' ) '
if(@companyname <> '')
set @sql = @sql + ' where (companyname like ''%' + @companyname + '%'' ) '
if(@companyaddress <> '')
set @sql = @sql + ' where (companyaddress like ''%' + @companyaddress + '%'' ) '
if(@familyaddress <> '')
set @sql = @sql + ' where (familyaddress like ''%' + @familyaddress + '%'' ) '
exec(@sql)
declare @sql varchar(400)
set @sql = 'select eafi_addresslist. *, case sex when 0 then ''男'' else ''女'' end as sexname from eafi_addresslist '
if(@name <> '')
set @sql = @sql + ' where ( name like ''%' + @name + '%'') '
if(@nickname <> '')
set @sql = @sql + ' where (nickname like ''%' + @nickname + '%'' ) '
if(@companyname <> '')
set @sql = @sql + ' where (companyname like ''%' + @companyname + '%'' ) '
if(@companyaddress <> '')
set @sql = @sql + ' where (companyaddress like ''%' + @companyaddress + '%'' ) '
if(@familyaddress <> '')
set @sql = @sql + ' where (familyaddress like ''%' + @familyaddress + '%'' ) '
exec(@sql)
set @sql = 'select eafi_addresslist. *, case sex when 0 then ''男'' else ''女'' end as sexname from eafi_addresslist where 1 = 1 '
+ case when @name != '' then ' and name like ''%''' + @name + '''%''' else '' end
+ case when @nickname != '' then ' and name like ''%''' + @nickname + '''%''' else '' end
+ case when @companyname != '' then ' and name like ''%''' + @companyname + '''%''' else '' end
+ case when @companyaddress != '' then ' and name like ''%''' + @companyaddress + '''%''' else '' end
+ case when @familyaddress != '' then ' and name like ''%''' + @familyaddress + '''%''' else '' end exec(@sql)