如何从SQL Server 2008获取待定，认证值和两个值 [英] How do I get pending, certified value and both values from SQL server 2008

问题描述

这里我有演示，其中我必须计算待定活动，认证活动和所有（待定+认证）。问题是我使用了



NULL => FOR PENDING

NOT NULL =>获得认证和

=> FOR ALL（PENDING + CERTIFIED）。



这些值来自c＃编码作为存储过程的参数。



我尝试过：



以下是我的存储过程

创建程序[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其中PACKAGEDESCR = @ PACKAGEDESCR和VOUCHERDT @VOUCHERFROMDT和@VOUCHERTODT 
和
 SUBACTIVITY = len（@RefNo）>的情况; 0然后@RefNo else SUBACTIVITY结束
和
 isnull（WDNO，'NULL'）= @ STATUS ='NULL'时的情况那么'NULL'
 WHEN @ STATUS ='NOT NULL'那么WDNO 
 ELSE WDNO 
 END 
 AND Contractor = 
当LEN（@CONTRACTOR）> 0然后@CONTRACTOR其他承包商
结束
结束时的情况

1.此查询适用于NULL VALUES，即待处理

 EXEC [PROC_DPRREGISTER1_1 ]'RML ACADEMY-LUCKNOW'，'2017-04-01'，'2017-04-20'，'NULL'，''，''

 2。此查询适用于NOT NULL VALUES，即CERTIFIED 
< pre> EXEC [PROC_DPRREGISTER1_1]'RML ACADEMY-LUCKNOW'，'2017-04-01'，'2017-04-20'，'NOT NULL'， ''，''

3.此查询适用于所有价值，即CERTIFIED + PENDING

 EXEC [PROC_DPRREGISTER1_1]'RML ACADEMY-LUCKNOW'，'2017-04-01'，'2017-04-20'，''，''，''

它适用于待定和认证，但当我有20条记录时，All.ie有问题115认证115记录然后全部应该是135但我的查询显示115意味着它再次显示认证记录

请帮助我..

谢谢。

解决方案

您可以使用单独的查询，例如：

  SELECT  * 来自 DPRReg_R2  WHERE  @ STATUS = ' ' 

否则会出现与NOT NULL的冲突，因为''也被视为NOT NULL

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.

解决方案

You could use a seperate query like:

SELECT * from DPRReg_R2 WHERE @STATUS=''

Otherwise there will be a conflict with NOT NULL, as '' is considered NOT NULL too.

这篇关于如何从SQL Server 2008获取待定，认证值和两个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！