I have one form in which i need to list the details of person(all columns coming from different table) when i select name of the person with respective state and click "ok " button till here it is ok but when i i donot select name of the person and click ok button .it will list me all persom with respective state as all persons are there in table "BPMTD" but i want only of respective state say"Punjab" whose state code is 28
I have one table "BPPAY" in database where i have list of persons with different state
I used following sp
ALTER procedure [SD].[SPS_RL_GetVat23test]
(
@FromDate varchar(50),
@ToDate varchar(50),
@CardCode varchar(50),
@DocCode varchar(50),
@Status varchar(50)
)
as
Begin
DECLARE @MyTableVar table(
[TinNo][varchar](100),
[CardName][varchar](150),
[Address] [varchar](2000),
[DocCode] [varchar](50),
[DocumentDate] Datetime,
[TaxCategoryDescription] [varchar](50),
[TotalBtax] [numeric](20,6),
[Partnertax] [numeric](20,6),
[Partnertaxsurcharge] [numeric](20,6)
)
insert into @MyTableVar(TinNo,CardName,Address,DocCode,DocumentDate,TaxCategoryDescription,TotalBtax,Partnertax ,Partnertaxsurcharge )
select ISNULL(( select TOP 1 TinNo from BP.BPTAX where CardCode=H.CardCode),'NA'),H.CardName,H.ShipToAddress,
H.DocCode,H.DocumentDate,ISNULL((select TOP 1 TaxRate from SD.ARTXM where DocCode=H.DocCode ),0.00),
ISNULL((select TOP 1 sum(TotalBTax) from sd.ARDTL where DocCode=H.DocCode ),0.00),
ISNULL(( select top 1 PartnerTaxSum from sd.ARTXM where FkTaxCode in(3,5,1) and DocCode=H.DocCode ),0.00),
ISNULL((select top 1 PartnerTaxSum from sd.ARTXM where FkTaxCode in(4,2,6) and DocCode=H.DocCode ),0.00)
from SD.ARHDR H
where(( @FromDate is null or @ToDate is null) or (H.PostingDate >= Convert(datetime,@FromDate)
and H.PostingDate<=Convert(datetime,@ToDate)))
and (@CardCode in (SELECT FkCardCode FROM bp.BPPAY where FkStateCode =28 ) or H.CardCode=@CardCode)
and(@Status is null or H.Status=@Status)
group by CardCode,CardName,ShipToAddress,H.DocCode,DocumentDate,H.ModTime
select * from @MyTableVar
end
SELECT FkCardCode FROM bp.BPPAY where FkStateCode =28 in bold for getting state punjab wiith code 28
and (@DocCode is null or H.DocCode=@DocCode)
i am not getting any result
any help will be highly appreciable
regards
shivani