How to write procedure to get records from table which i Need
I need to fetch columns from table (single) which satisfy recoed beetween sdate and edate and code_name and Too and dc. These five columns must match. rest rest column is optional. User may put input or not.
Then house should i pass null value which user is not supplying and get records according to user supplied inputs.
ALTER PROCEDURE [dbo].[sp_Cnmt_Find]
(
@sdate date
,@edate date
,@CODE_NAME varchar(20)
,@TOO varchar (40)
--,@DC varchar (2)
,@CONSIGNOR varchar(40)
,@CONSIGNEE varchar(40)
,@PVTMARKA VARCHAR (30)
,@NOPKG varchar(6)
,@GOODS varchar(40)
,@GTOTAL numeric (12,2)
)
as
begin
select *from CONSIGNMENT
where CODE_NAME=@CODE_NAME and TOO=@TOO and (CNDT BETWEEN @sdate AND @edate)or CONSIGNOR like '%'+ISNULL(@CONSIGNOR,'')+'%'
or CONSIGNEE like '%'+ISNULL(@CONSIGNEE,'')+'%'
and PVTMARKA like '%'+ISNULL(@PVTMARKA,'')+'%'
or NOPKG like '%'+ISNULL(@NOPKG,'')+'%' or GOODS like '%'+ISNULL(@GOODS,'')+'%'
or GTOTAL like '%'+ISNULL(@GTOTAL,'')+'%'
SET NOCOUNT ON;
end