如果给定错误的参数ID，如何从存储过程中获取默认值?

I have a stored procedure like this:

我有这样一个存储过程:

alter procedure [dbo].[carcallvalidation]
@carid nvarchar(100)=null
as
begin
    select count(t.TBarcode) as barcodeCount, t.Paid,t.Status,t.DelDate
    from Transaction_tbl t
    where TBarcode=@carid

    group by t.paid,t.status,t.DelDate

    declare @transid integer=null;
   select @transid= t1.transactID from Transaction_tbl t1 where TBarcode=@carid;
if(select count(k.transactid) from KHanger_tbl k where k.transactid=@transid)>0
begin
return 1
end
end

If I pass carid a wrong number, an empty table is returned; but I'd like it to instead get T.Barcode count as 0.

如果我传递给carid一个错误的号码，则返回一个空表;但是我希望它得到T。条形码计数为0。

How can I do that?

我该怎么做呢?

2 个解决方案

#1

you have to remove filter in from where condition then .

你必须把滤镜从那里移除。

Try following store procedure:

试试以下存储过程:

ALTER procedure [dbo].[carcallvalidation111]
@carid nvarchar(100)=null
as
begin
select count(case when t.TBarcode is null or t.TBarcode!=@carid then 0 else 1 end) as cnt,  t.Paid,t.Status,t.DelDate
from Transaction_tbl t
group by t.Paid,t.Status,t.DelDate;

declare @transid integer=null;

select @transid= t1.transactID from Transaction_tbl t1 where TBarcode=@carid;
if(select count(k.transactid) from KHanger_tbl k where k.transactid=@transid)>0
begin
return 1
end
end

#2

i would suggest you to try this.

我建议你试试这个。

    alter procedure [dbo].[carcallvalidation]
    @carid nvarchar(100)=null,
@countTbar int 
    as
    begin
        select @countTbar=  select count(t.TBarcode) as barcodeCount
        from Transaction_tbl t
        where TBarcode=@carid  group by t.paid,t.status,t.DelDate
 if(@countTbar is null)
    begin
    return 0
    end

        select count(t.TBarcode) as barcodeCount, t.Paid,t.Status,t.DelDate
        from Transaction_tbl t
        where TBarcode=@carid

        group by t.paid,t.status,t.DelDate

        declare @transid integer=null;
       select @transid= t1.transactID from Transaction_tbl t1 where TBarcode=@carid;
    if(select count(k.transactid) from KHanger_tbl k where k.transactid=@transid)>0
    begin
    return 1
    end
    end

#1