如何在sql中的sp中使用子查询? [英] how to use subquery in sp in sql?
问题描述
我有一个表单,我需要列出人员的详细信息(来自不同表的所有列),当我选择具有相应状态的人的姓名并单击确定按钮,直到这里它是好的,但是当我没有选择该人的姓名,然后点击确定按钮。由于所有人都在表BPMTD中,所有人都在那里,我会列出所有人的状态,但我只想要各州说旁遮普,其州代码为28
我在数据库中有一个表BPPAY,我有不同州的人员名单
我用后跟sp
ALTER procedure [SD]。[ SPS_RL_GetVat23test]
- [SD]。[SPS_RL_GetVat23test] null,null,null,null,null
- 这里@CardCode将为null,因为我还没有选择蚂蚁名字f rom前端
(
@ FromDate varchar ( 50 ),
@ ToDate varchar ( 50 ),
@ CardCode varchar ( 50 ),
@ DocCode varchar ( 50 ),
@ Status varchar ( 50 )
)
as
开始
DECLARE @ MyTableVar table (
[TinNo] [ varchar ]( 100 ),
[名称] [ varchar ]( 150 ),
[地址] [ varchar ]( 2000 ),
[ DocCode] [ varchar ]( 50 ),
[DocumentDate] 日期时间,
[TaxCategoryDescription] [ varchar ]( 50 ),
[TotalBtax] [数字]( 20 , 6 ),
[Partnertax] [数字]( 20 , 6 ),
[Partnertaxsurcharge] [数字]( 20 , 6 )
)
insert 进入 @ MyTableVar (TinNo,CardName,Address,DocCode,DocumentDate,TaxCategoryDescription,TotalBtax,Partnertax,Partnertaxsurcharge)
选择 ISNULL((选择 TOP < span class =code-digit> 1 TinNo 来自 BP.BPTAX 其中 CardCode = H.CardCode),' NA'),H.CardName,H.ShipToAddress,
H.DocCode,H.DocumentDate,ISNULL(( select TOP 1 TaxRate 来自 SD.ARTXM 其中 DocCode = H.DocCode), 0 。 00 ),
ISNULL(( select TOP 1 sum(TotalBTax)来自 sd.ARDTL 其中 DocCode = H.DocCode), 0 。 00 ),
ISNULL( (选择 top 1 PartnerTaxSum 来自 sd.ARTXM 其中 FkTaxCode in ( 3 , 5 , 1 )和 DocCode = H.DocCode), 0 。 00 ),
ISNULL((选择 top 1 PartnerTaxSum 来自 sd.ARTXM 其中 FkTaxCode ( 4 , 2 , 6 )和 DocCode = H.DocCode), 0 。< span class =code-digit> 00 )
来自 SD.ARHDR H
where (( @ FromDate null 或 @ ToDate null )或(H.PostingDate> = 转换( datetime , @ FromDate )
和 H.PostingDate< = Convert( datetime , @ ToDate )))
和 ( @ CardCode ( SELECT FkCardCode FROM bp.BPPAY 其中 FkStateCode = 28 )或 H.CardCode=@CardCode)
和 ( @ Status null 或 H.Status=@Status)
group by CardCode,CardName,ShipToAddress,H.DocCode,DocumentDate,H.ModTime
选择 * 来自 @ MyTableVar
end
- 我写了
< span class =code-keyword> SELECT FkCardCode FROM bp.BPPAY 其中 FkStateCode = 28 粗体 获取州旁遮普代码 28
和( @ DocCode 是 null 或 H.DocCode=@DocCode)
i我没有得到任何结果
任何帮助都会非常值得一提/>
问候
shivani
你听说过动态查询吗?
在sql server /中执行动态sql命令 [ ^ ]
在存储过程中构建动态SQL [ ^ ]
在sql server中生成动态sql语句 [ ^ ]
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]
-- [SD].[SPS_RL_GetVat23test] null,null,null,null,null
--here @CardCode will be null as i have not selecting ant name from front end
(
@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
-- i wrote
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
Have you ever heard about dynamic queries?
Execute dynamic sql commands in sql server/[^]
Building Dynamic SQL In a Stored Procedure[^]
Generate dynamic sql statements in sql server[^]
这篇关于如何在sql中的sp中使用子查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!