I have a huge stored procedure which calls other mini-ones. The problem is that I need a temp table and I'd like to avoid a real table being used as a temp one.
You've probably guessed by now, but if you haven't, a table variable is created at the beginning of the main stored procedure and will be (or needs to be) used by the mini stored procedures.
For example, main sp has modified the variable table data. A mini sp runs within the main sp and adds more data to this variable table.
The declaration is fine, but when I try to use it as a parameter for a mini sp, I get an error:
"Msg 156, Level 15, State 1, Procedure CalculateHoursAlreadyWorkedThisCycleDup, Line 11
Incorrect syntax near the keyword 'table'."
Here's how I'm trying to use it:
ALTER PROCEDURE [dbo].[CalculateHoursAlreadyWorkedThisCycleDup] @SSN char(9), @currentPayrollDate smalldatetime, @std_hours float, @tblACRWeekHours_temp table --is this possible? AS ....
if this isn't possible, what could be a work around? TIA.