Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-10-26 : 20:26:07
|
Andy writes "I have a table called debtors, with fields DBDebtorCode,DBLinkID,DBCollector,DBClientCode,DBAccountNo,DBSIN.
I am passing one variable,DBDebtorCode, to a stored proc. What I have to find is a recordset of all debtors(DBDebtorCode) that have the same DBSIN as that of DBDebtorCode that was passed OR the same DBClientCode and DBAccountNo. Once I have that I have to find the maximum DBLinkID from all records returned. Apply that maxLinkID to all records returned and apply the DBcollector associated with the maxLinkID to all records returned.
I used a union to create on recordset but am having trouble with the maxLinkID. See code below.....
CREATE PROCEDURE [dbo].[dblinknoparm]
(@DBDebtorCode [varchar](20))
AS
DECLARE @DBSIN [int]
DECLARE @maxDBLinkID [int]
DECLARE @collector [varchar](3)
DECLARE @DBClientID[varchar](20)
DECLARE @DBAccountNo [varchar](40)
DECLARE @debtors [varchar](20)
**Find ClientID assoc. with DebtorCode passed**
SET @DBClientID = (SELECT DBClientID FROM Debtors WHERE@DBDebtorCode = DBDebtorCode)
**Find AccountNo assoc. with DebtorCode passed**
SET @DBAccountNo = (SELECT DBAccountNo FROM Debtors WHERE @DBDebtorCode = DBDebtorCode)
**Find DBSIN assoc. with DebtorCode passed**
SET @DBSIN = (SELECT DBSIN FROM Debtors WHERE @DBDebtorCode = DBDebtorCode )
**This returns all the records I need**
SELECT DBDebtorCode,DBLinkID,DBUserCode FROM Debtors WHERE @DBSIN = DBSIN
UNION
SELECT DBDebtorCode,DBLinkID,DBuserCode FROM Debtors WHERE @DBClientID = DBClientID AND @DBAccountNo = DBAccountNo
I can't put the results of the union into a variable to get the maxLinkID so how do I find the maxLinkID ??" |
|
|
|
|
|
|
|
Find the max number from multiple selects.