August 4, 2014 at 8:52 am
Hello all! I've created a stored procedure to update a table variable with specific info, then query the table variable to show the results.
When I run the same statements individually (not within a stored procedure) and using an actual table in lieu of the table variable, everything executes as expected and provides the correct results. After putting it all in a stored procedure, I get the infamous "must declare scalar variable" on my table variable @IA_CourtCostReport.
Any ideas? I have checked and re-checked for missing commas, parentheses, misspelled variables, etc. and have found no errors. Gotta be a syntax issue.... any input would be most appreciated! Here is the code for the SP:
CREATE PROCEDURE sp_Custom_IA_CourtCostReport
@StartDate datetime,
@EndDate datetime
AS
BEGIN
DECLARE @IA_CourtCostReport TABLE
(
AttyID INT NOT NULL PRIMARY KEY,
Name varchar(50),
AttyState varchar(3),
CCspent money,
CCrecovered money,
Interest money
)
--Insert attyIDs for all attorneys with payments received
INSERT INTO @IA_CourtCostReport (AttyID)
SELECT DISTINCT AttorneyId
FROM payhistory
WHERE datepaid BETWEEN @StartDate AND @EndDate
AND AttorneyID IS NOT NULL
AND AttorneyID > 0
--Update attorney name and state
UPDATE @IA_CourtCostReport
SET Name = attorney.Name,
AttyState = attorney.[State]
FROM attorney
WHERE @IA_CourtCostReport.AttyID = attorney.AttorneyId
--Update total CC spent
UPDATE @IA_CourtCostReport
SET CCspent =
COALESCE
(
(
SELECT SUM(LL.DebitAmt)
FROM Legal_Ledger AS LL
INNER JOIN master
ON LL.AccountID = master.number
WHERE LL.ItemDate BETWEEN @StartDate and @EndDate
AND master.AttorneyID > 0
AND master.AttorneyID = @IA_CourtCostReport.AttyID
)
,0
) * -1
--Get total CC recovered
UPDATE @IA_CourtCostReport
SET CCrecovered =
COALESCE
(
(
SELECT SUM(LL.CreditAmt)
FROM Legal_Ledger AS LL
INNER JOIN master
ON LL.AccountID = master.number
WHERE LL.ItemDate BETWEEN @StartDate and @EndDate
AND master.AttorneyID > 0
AND master.AttorneyID = @IA_CourtCostReport.AttyID
AND LL.LegalLedgerTypeID NOT IN
(
'8' --Bad legal - ERA Costs
,'15' --Billed in error
,'16' --Overpayment offset
,'40' --Costs denied in Jmt
)
)
,0
)
--Get total interest
UPDATE @IA_CourtCostReport
SET Interest =
COALESCE
(
(
SELECT SUM(paid9)
FROM payhistory
WHERE paytype = 'Paid Us'
AND datepaid BETWEEN @StartDate and @EndDate
AND AttorneyID > 0
AND AttorneyID = @IA_CourtCostReport.AttyID
), 0
)
--final select statement
SELECT Name, AttyState AS 'State', CCspent,
CCrecovered, CCspent+CCrecovered AS 'NetCC',
Interest, CCspent+CCrecovered+Interest as NetTotal
FROM @IA_CourtCostReport
WHERE CCspent+CCrecovered+Interest != 0
UNION
SELECT DISTINCT 'Subtotal', AttyState, SUM(CCspent),
SUM(CCrecovered), SUM(CCspent)+SUM(CCrecovered),
SUM(Interest), SUM(CCspent)+SUM(CCrecovered)+SUM(interest)
FROM @IA_CourtCostReport
GROUP BY AttyState
UNION
SELECT 'GRAND TOTAL', 'XXX', SUM(CCspent),
SUM(CCrecovered), SUM(CCspent)+SUM(CCrecovered),
SUM(Interest), SUM(CCspent)+SUM(CCrecovered)+SUM(interest)
FROM @IA_CourtCostReport
ORDER BY AttyState, Name
END
GO
I get the red squiggly underline on WHERE @IA_CourtCostReport.AttyID but can't figure out what's wrong with that.
Thanks in advance for helping a newby! 🙂
August 4, 2014 at 9:09 am
You need to alias table variables in the FROM, and use the aliases in the other clauses. You can't refer to @tbl.somecolumn anywhere, it's not considered valid
btw, are table variables a good idea here? Not a performance problem compared to equivalent temp table?
Edit, besides that, you have UNION instead of UNION ALL, since the result sets cannot overlap there's no point in telling SQL to remove duplicates, as well as some unnecessary DISTINCTs.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2014 at 9:37 am
GilaMonster (8/4/2014)
You need to alias table variables in the FROM, and use the aliases in the other clauses. You can't refer to @tbl.somecolumn anywhere, it's not considered valid
Which FROM are you referring to? What's the correct way to reference a column in the table variable if @tbl.somecolumn isn't valid?
btw, are table variables a good idea here? Not a performance problem compared to equivalent temp table?
I have no clue. LOL I'm new to SQL and MS SQL Server.... was drafted by management to handle this stuff since I'm the nerdiest employee in the company. 🙂 The resulting dataset is only a handful of rows (about 6 or so), so I doubt very much if there would be performance problems as a result of table variable vs. temp table.... but I really have no idea.
Edit, besides that, you have UNION instead of UNION ALL, since the result sets cannot overlap there's no point in telling SQL to remove duplicates, as well as some unnecessary DISTINCTs.
I'm just now learning about the differences between things like UNION and UNION ALL, so thank you for clarifying best practice here. As for DISTINCT, I used it once in the query because without it the resulting dataset had everything duplicated.
August 4, 2014 at 9:45 am
iasher (8/4/2014)
GilaMonster (8/4/2014)
You need to alias table variables in the FROM, and use the aliases in the other clauses. You can't refer to @tbl.somecolumn anywhere, it's not considered validWhich FROM are you referring to? What's the correct way to reference a column in the table variable if @tbl.somecolumn isn't valid?
The FROM of any query that you have a table variable in. You need to specify an alias for the table variable in the FROM clause and then use the aliased name everywhere else, like in column references
....
FROM @SomeTable t -- <-- The aliased table
WHERE t.SomeColumn = 4;
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2014 at 9:47 am
That syntax you use in your UPDATE doesn't look right. I've certainly never seen it done like that before. Here's how I'd have done it:
UPDATE i
SET Name = a.Name,
AttyState = a.[State]
FROM attorney a JOIN @IA_CourtCostReport i
ON i.AttyID = a.AttorneyId
John
August 4, 2014 at 10:19 am
GilaMonster (8/4/2014)
The FROM of any query that you have a table variable in. You need to specify an alias for the table variable in the FROM clause and then use the aliased name everywhere else, like in column references
....
FROM @SomeTable t -- <-- The aliased table
WHERE t.SomeColumn = 4;
Still not sure how to apply this. I believe this is the UPDATE statement that is causing the problem:
--Update attorney name and state
UPDATE @IA_CourtCostReport
SET Name = attorney.Name,
AttyState = attorney.[State]
FROM attorney
WHERE @IA_CourtCostReport.AttyID = attorney.AttorneyId
The table referenced in the FROM clause ("attorney") is actually the source table, but it's the target table (@IA_CourtCostReport) that's causing the problem, so I'm not sure how to reference that with an alias when it shouldn't be in the FROM clause. I'm not getting any data FROM the table variable.
The table variable is referenced without aliases in the FROM clauses of the final SELECT statement at the end and that part executes just fine.
Not doubting your expertise, just trying to find what I'm missing here....
August 4, 2014 at 10:29 am
iasher (8/4/2014)
The table referenced in the FROM clause ("attorney") is actually the source table, but it's the target table (@IA_CourtCostReport) that's causing the problem, so I'm not sure how to reference that with an alias when it shouldn't be in the FROM clause. I'm not getting any data FROM the table variable.
Yes, it should be in the FROM clause. It has to be in this case because you cannot refer to it in your join condition otherwise.
If you look in Books Online, the full form of the update.. from is
UPDATE <target>
FROM <target> <join> <source> ON ...
Which means you can (and need to) define a table alias for the table variable.
The SELECT is fine because you never try to do @TableVar.ColumnName, which is what is not allowed.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply