Click here to Skip to main content
15,914,419 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
Posted
Updated 7-Jul-17 0:37am

1 solution

You could use a seperate query like:
SQL
SELECT * from DPRReg_R2 WHERE @STATUS=''
Otherwise there will be a conflict with NOT NULL, as '' is considered NOT NULL too.
 
Share this answer
 
Comments
SujataJK 7-Jul-17 7:01am    
thanks @RickZeeland but @status='' here '' it used for pending + certified value.
i.e it should display both pending and certified values.
SujataJK 7-Jul-17 7:10am    
I could not use a separate query like you above shown.Here i have dropdownlist with values Pending ,Certified, All .Where My table has WDNO column with NULL means PENDING AND Some value like 'WDNLAK/00008/17-18' means CERITIFIED .
When i choose All option from dropdownlist then result contain pending records + certified records.For that purpose i have use Case statement in above my query.
RickZeeland 7-Jul-17 7:18am    
Maybe it is simpler to create separate stored procedures for each case, then use queries.
RickZeeland 7-Jul-17 7:20am    
For 'ALL' you don't need a where clause, right ?
SujataJK 7-Jul-17 7:28am    
yes

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900