Hi,
here i have demo in which i have to calculate pending activity,certified activity and All(Pending+Certified).Problem is that i have used
NULL => FOR PENDING
NOT NULL=> FOR CERTIFIED AND
"" => FOR ALL(PENDING + CERTIFIED).
these value comes from c# coding as parameter to stored procedure.
What I have tried:
Following is my stored procedure
create Procedure [dbo].[PROC_DPRREGISTER1_1] @PACKAGEDESCR VARCHAR(50),@VOUCHERFROMDT VARCHAR(20),@VOUCHERTODT VARCHAR(20),@STATUS VARCHAR(10),@RefNo varchar(10),@CONTRACTOR varchar(50)
AS
begin
--Declare @qry;
--set @qry=
select * from DPRReg_R2 where PACKAGEDESCR=@PACKAGEDESCR AND VOUCHERDT between @VOUCHERFROMDT AND @VOUCHERTODT
AND
SUBACTIVITY = Case when len(@RefNo) >0 then @RefNo else SUBACTIVITY End
And
isnull(WDNO,'NULL') = case when @STATUS='NULL' THEN 'NULL'
WHEN @STATUS='NOT NULL' THEN WDNO
ELSE WDNO
END
AND Contractor=
Case when LEN(@CONTRACTOR)>0 then @CONTRACTOR else Contractor
end
end
1. This query is for NULL VALUES i.e for Pending
EXEC [PROC_DPRREGISTER1_1] 'RML ACADEMY-LUCKNOW' ,'2017-04-01','2017-04-20','NULL','',''
2. This query is for NOT NULL VALUES i.e for CERTIFIED
<pre>EXEC [PROC_DPRREGISTER1_1] 'RML ACADEMY-LUCKNOW' ,'2017-04-01','2017-04-20','NOT NULL','',''
3. This query is for ALL VALUES i.e for CERTIFIED+PENDING
EXEC [PROC_DPRREGISTER1_1] 'RML ACADEMY-LUCKNOW' ,'2017-04-01','2017-04-20','','',''
Its works fine for pending and certified but something wrong with All.i.e when i have pending 20 records and 115 certified 115 records then All should be 135 but my query shows 115 means it again shows certified records only
Please help me..
thanks.