我在存储 SQL 过程中遇到语法问题

Question

我正在尝试向存储过程添加三列(我在存储过程中没有太多经验(，这些列是家庭 ID、地址和电话号码。尽管我已经运行了查询并确认了 select 语句的语法正确，但当我将该语句添加到存储的进程中时，我收到以下错误：'capPrograms' 附近的语法不正确(这将是第 14 行(。我知道这可能与锄头有关，我正在尝试在更新过程中实现我的语句，但我不太确定问题是什么，因为我尝试了几次类似或更广泛的错误的更改。感谢任何帮助，我在下面包含了我正在使用的代码块：

SELECT  capCase.Id AS capCase_Id,
capCase.DateApplied AS capCase_DateApplied,
CASE 
WHEN ISNUMERIC(#cases.origCaseYear)=1 THEN CAST(origCaseYear + 2004 AS VARCHAR) + ' - ' + CAST(capCase.IdYear + 2004 AS VARCHAR)
WHEN #cases.maxRolloverYear IS NOT NULL THEN CAST(capCase.IdYear + 2004 AS VARCHAR) + ' - ' + CAST(#cases.MaxRolloverYear + 2004 AS VARCHAR)
ELSE CAST(capCase.IdYear + 2004 AS VARCHAR) 
END AS capCase_IdYear,
Person.id as Person_id,
Person.LastName + ', ' + Person.FirstName AS PersonPrint_Name,
person.idFamily AS Family_ID, person.homePhone AS Phone_No,  (SELECT family.physicalAddress1+ ', ' + family. physicalAddressCity+ ' ' +family. physicalAddressZip) AS Address
FROM Family
LEFT JOIN person ON family.Id = person.idFamily;
capPrograms.Program AS capPrograms_Program,
capStatus.Status AS capStatus_Status,
#lastFollowup.followupDate AS capCaseFollowup_Date,
CASE
WHEN capCase.IdCaseWorker IS NULL THEN 'No Worker Assigned'
ELSE caseWorker.LastName + ', ' + caseWorker.FirstName + ISNULL('<' + caseWorker.EmailWork + '>', '')
END AS capCase_IdCaseWorker, 
capcaseDenialReason.reason AS capCase_idDenialReason, 

Answer 1

太长了，无法注释，所以我在您的代码中添加了一堆注释。你有，很多问题。

SELECT  
capCase.Id AS capCase_Id,
capCase.DateApplied AS capCase_DateApplied,
CASE 
--here you are referencing a temp table, which isn't joined below
WHEN ISNUMERIC(#cases.origCaseYear)=1 THEN CAST(origCaseYear + 2004 AS VARCHAR) + ' - ' + CAST(capCase.IdYear + 2004 AS VARCHAR)
WHEN #cases.maxRolloverYear IS NOT NULL THEN CAST(capCase.IdYear + 2004 AS VARCHAR) + ' - ' + CAST(#cases.MaxRolloverYear + 2004 AS VARCHAR)
ELSE CAST(capCase.IdYear + 2004 AS VARCHAR) 
END AS capCase_IdYear,
Person.id as Person_id,
Person.LastName + ', ' + Person.FirstName AS PersonPrint_Name,
person.idFamily AS Family_ID, 
person.homePhone AS Phone_No,  
--this subquery as no from clause... that's an issue... you probably want to remove it completly and replace it with the one below
(SELECT family.physicalAddress1+ ', ' + family. physicalAddressCity+ ' ' +family. physicalAddressZip) AS Address,
--this is likely what you want
family.physicalAddress1 + ', ' + family. physicalAddressCity + ' ' + family. physicalAddressZip AS AddressCorrected
FROM Family
LEFT JOIN person ON 
family.Id = person.idFamily;   --you have a ; here, which terminates the statement. Everything else below isn't included
--Here you need to join the capPrograms and #lastFollowup table..
--here, you listed some columns, without a table reference. You need to join the table above, and move these columns above the FROM clause
capPrograms.Program AS capPrograms_Program,
capStatus.Status AS capStatus_Status,
#lastFollowup.followupDate AS capCaseFollowup_Date,
CASE
WHEN capCase.IdCaseWorker IS NULL THEN 'No Worker Assigned'
ELSE caseWorker.LastName + ', ' + caseWorker.FirstName + ISNULL('<' + caseWorker.EmailWork + '>', '')
END AS capCase_IdCaseWorker, 
capcaseDenialReason.reason AS capCase_idDenialReason,