T-SQL single parameter will perform like search on multiple table column



 ALTER PROCEDURE [dbo].[USP_GetPickerNameList]  
       @SearchStr varchar(50)  
 AS  
 BEGIN  
 SET @SearchStr = RTRIM(@SearchStr) + '%'  
 SELECT DISTINCT TOP(10)  
       p.cpickerid  
      ,p.cpickername  
      ,p.cpickeridtype  
      ,p.cid  
      ,p.cidissuingstate  
      ,p.pickerid_PK  
      ,p.pickeridtype_FK  
      ,p.idissuingstate_FK  
      ,c.ccusname  
      ,c.cusid_PK  
      ,c.dbirthday  
      ,f.caddress1  
 FROM pickers p  
 INNER JOIN pickergroups  
      ON p.pickerid_PK = pickergroups.pickerid_FK  
 INNER JOIN customer c  
      ON c.pickergroupid_FK = pickergroups.pickergroupid_PK  
      LEFT OUTER JOIN family f on c.familyID_FK = f.familyID_PK   
      LEFT OUTER JOIN rx r on c.cusId_PK = r.cusId_FK  
 WHERE  RTRIM(LTRIM(p.cpickername)) LIKE @SearchStr  
           OR RTRIM(LTRIM(c.ccusname)) LIKE @SearchStr  
           OR RTRIM(LTRIM(c.ccusfirstname)) LIKE @SearchStr  
           OR RTRIM(LTRIM(c.ccuslastname)) LIKE @SearchStr  
           OR RTRIM(LTRIM(r.rxno_PK)) LIKE @SearchStr  
 ORDER BY p.cpickername  
 END  


Popular posts from this blog

WPF Crystal Report Viewer Using SAP

ASP.NET MVC razor SAP Crystal report