Can a table variable be used as an arg. for a stored procedure..?

[Link82]

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.

[Ramesh S]

Check these links:

Passing a Table to A Stored Procedure

Table-Valued parameter in SQL Server 2005

In SQL Server 2008,

Table-Valued Parameters (Database Engine)

[sknake]

I would follow the advice Ramesh gave but you can also access a #Temporary table created before calling the sproc. That does limit how and where you can call your sproc from though and is probably not a good idea:

IF OBJECT_ID('sp_Test123') IS NOT NULL DROP PROCEDURE sp_Test123

GO

CREATE PROCEDURE dbo.sp_Test123
WITH EXECUTE AS CALLER
AS
BEGIN
  Select * From #Temp
END

GO

IF OBJECT_ID('tempdb..#Temp', 'U') IS NOT NULL DROP TABLE #Temp
Create Table #Temp
(
  Col1 varchar(30),
  Col2 varchar(30)
)

Insert Into #Temp (Col1, Col2) Values ('a', 'b')

GO

exec dbo.sp_Test123