ASP.NET - How to write proc for getting user specifyed multi inpute using like

Asked By bhanupratap singh on 28-Feb-13 01:51 AM
ALTER PROCEDURE [dbo].[sp_Cnmt_Findd]
(
@sdate as date
,@edate as date
    ,@CODE_NAME varchar(20)
    ,@TOO  as varchar (40)
   -- ,@DC varchar (2)
    ,@CONSIGNOR as varchar(MAX)=null
    ,@CONSIGNEE as varchar (MAX)=null
    ,@PVTMARKA as VARCHAR (MAX)=null
    ,@NOPKG as varchar(MAX)=null
    ,@GOODS as varchar(MAX)=null
   -- ,@GTOTAL as numeric(12, 2)
)
as
begin
select * from CONSIGNMENT
where CODE_NAME=@CODE_NAME and TOO=@TOO and (CNDT BETWEEN @sdate AND @edate) and
 (@CONSIGNOR is null or @CONSIGNOR like '%'+@CONSIGNOR+'%')  and
 (@CONSIGNEE is null or CONSIGNEE like '%'+@CONSIGNEE+'%')  and
 (@PVTMARKA is null or PVTMARKA like '%'+@PVTMARKA+'%')  and
 (@NOPKG is null or NOPKG =@NOPKG)  and
 (@GOODS is null or GOODS like '%'+@GOODS+'%') 
end

user may leave some column blank or can fill input
Robbe Morris replied to bhanupratap singh on 28-Feb-13 09:15 AM

declare @RealVariable varchar(100)
declare @likeClause varchar(300)
 
 set @likeClause = '%' + @RealVariable + '%'


 select *
   from tableA
   where colA like @likeClause

Yep, I know it is a pain but it is what you have to do with LIKE clauses.
bhanupratap singh replied to Robbe Morris on 08-Mar-13 12:00 AM
thanks