时间戳

首页 > 解决方案 > 存储过程中的输出参数问题

stored-procedures - 存储过程中的输出参数问题

问题描述

我有 2 张桌子Customer和Beneficiary. 一个客户手机号码添加了 2 个或更多受益人。我已经创建了一个存储过程来获取与该手机号码相关的受益人姓名

create Proc Customer
(
@Mobile bigint,

@Beneficiary  varchar(100) out,

@Beneficiary2 varchar(100) out  
)
as

Begin

Select @Beneficiary = Beneficiary_Name, @Beneficiary2 = Beneficairy_Name from Beneficiary_master
as a 

join Customer_Master as b
on a.CustomerID= b.CustomerID

where Mobile_Number = @Mobile

End

Declare @Beneficiary varchar(100)

Declare @Beneficiary2 varchar(100)

Exec spCustomer 999999999, @Beneficiary out, @beneficiary2 out

如何获得两个受益人姓名，因为我只有一个名字

标签： stored-procedures

解决方案

您的 Select Query 将返回与 select 语句匹配的记录，并将最后一条记录的结果分配给变量@Beneficiary和@Beneficiary2

您必须更改选择查询以获得所需的 o/p。请尝试此语句，如果适合您的要求，请相应更改 SP

Declare  @Mobile bigint =123

Declare @Beneficiary  varchar(100) 

Declare @Beneficiary2 varchar(100) 

Select @Beneficiary = Beneficiary_Name 
from Beneficiary_master as a 
join Customer_Master as b on a.CustomerID= b.CustomerID
where Mobile_Number = @Mobile
ORDER BY Beneficiary_Name ASC
OFFSET  0 ROWS 
FETCH NEXT 1 ROWS ONLY

Select @Beneficiary2 = Beneficiary_Name 
from Beneficiary_master as a 
join Customer_Master as b on a.CustomerID= b.CustomerID
where Mobile_Number = @Mobile
ORDER BY Beneficiary_Name ASC
OFFSET  1 ROWS 
FETCH NEXT 1 ROWS ONLY

Select @Beneficiary Beneficiary, @Beneficiary2 Beneficiary2

推荐阅读