Is there a way to pass a table variable to function or Stored Proc...

[calvinsinger]

Hi,

Can you please tell me if there is a way to pass a table variable to a function or Stored Proc..., when calling it from another stored procedure.

The problem I am facing is that I am calling a function
from within a stored procedure. And within this function which I am calling, I want to be able to do a select from a temporary table. This temp table is being created by the calling procedure. And SQL is not allowing me to access a temporary table from within the function.

I think I can solve this problem, if I use a table variable instead of a temporary table, within the function. But I do not know how to pass a table varible as an argument/parameter to the function being called. I do not know if it can be done at all.

Help appreciated. Thank you

Calvin

 

[obislavu]

CREATE PROCEDURE dbo.sp_Delete
(
@TableName varchar(50),
@ItemD varchar(50),
)
AS
SET NOCOUNT ON
Set @WhereClause='WHERE ExceptionID=' + CAST(@ItemIdID as varchar(50))
Set @Cmd='DELETE FROM ' + @TableName + ' ' + @WhereClause
EXEC EXEC(@Cmd)
RETURN (1)
)

-obislavu-

 

[calvinsinger]

Thank you for response.

I am having trouble following it though. Can you pls. give me a synopsis of what you are trying to do.

In my problem scenario, the main procedure is actually calling a function. Inside the function, I am not allowed
to access a temporary table, nor am I allowed to use 'EXEC'.
So that is the problem. I am allowed to use a table variable, but the calling procedure needs to pass a populated table variable to the function being called. And how to pass the table variable to called function is my problems.