我如何将临时变量设置为基于另一个表的值 [英] How do i set a temp variable to a value based from another table
本文介绍了我如何将临时变量设置为基于另一个表的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用 AdventureWorks 2012 数据库,我对这个数据库非常着迷,
I am using the AdventureWorks 2012 database and I'm absolutely stumped on this one,
到目前为止我有
alter proc pName
(
@TranID int
)
as
declare @AccountID int
declare @Entered datetime
declare @Type char
declare @Amount money
declare @Service money
declare @WithdrawalDecrease smallint
declare @WithdrawalCount smallint
set @AccountID = (select AccountID
from Transactions
where TransID = @TranID)
set @WithdrawalCount = (select WithdrawalCount
from Accounts
inner join Transactions on Transactions.AccountID = Accounts.AccountID
where Transactions.AccountID = @AccountID)
但是取值的变量我做错了什么?
But the variable taking the value what am I doing wrong?
推荐答案
语法没有问题
SET @AccountID = (SELECT AccountID
FROM Transactions
WHERE TransID = @TranID)
SET @WithdrawalCount = (SELECT WithdrawalCount
FROM Accounts
INNER JOIN Transactions
ON Transactions.AccountID = Accounts.AccountID
WHERE Transactions.AccountID = @AccountID)
但是在这里您尝试将 AccountID 设置为 @AccountID for TransID =@TranID.如果您的 Transactions 表中有多个 @TranID 行,那么最后插入的值将分配给变量所以尝试使用 top 1
和 order by
But here you are trying to set AccountID to @AccountID for TransID =@TranID. If your Transactions table has more than one row for @TranID then the last inserted value will be assigned to the variable
so try using top 1
with order by
SET @AccountID = (SELECT top 1 AccountID
FROM Transactions
WHERE TransID = @TranID order by column)
SET @WithdrawalCount = (SELECT top 1 WithdrawalCount
FROM Accounts
INNER JOIN Transactions
ON Transactions.AccountID = Accounts.AccountID
WHERE Transactions.AccountID = @AccountID order by column)
这篇关于我如何将临时变量设置为基于另一个表的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文