Using Table variable as input to Function or Procedure

[Herrick Spencer]

Is there any way to use table variable as input to a function or stored procedure?

[Kurt]

By "Table Variable" I am assuming you mean a table column. If that is the case then, yes, you can pass a column to stored procs a couple of ways. Here are two ways that I know of:

1. You can create a cursor and get access to your table data row by row and pass it to an sp. Something like this.

Declare @lColumn1 VariableType
Declare MyCursor Cursor FORWARD_ONLY
For Select Column1 From MyTable
Open MyCursor
Fetch Next From MyCursor Into @lColumn1
While @@Fetch_Status <> -1
Begin
Exec MyStoreProc @lColumn1
Fetch Next From MyCursor Into @lColumn1
End
Close MyCursor

--OR--

2. You could use a UDF (assumeing you have SQL 2000)
CREATE FUNCTION [dbo].[MyFunction] (@pMyColumn VariableType)
RETURNS ReturnVariableType AS
Begin
DECLARE @lRetVal int
Exec MyStoredProc @pMyColumn
-- I don&#39;t know if your stored proc is returning and error
-- But for this example, let just say you&#39;ll always return a 1
Set @lRetVal=1
Return (@lRetVal)
End

Then you can call the UDF like this.
Select dbo.MyFunction(MyColumn) From MyTable

SQL will execute the UDF for every row in the table or for whatever you
specify in the Where clause.


Hope this helps



------------
Herrick Spencer at 7/9/01 1:32:48 PM


Is there any way to use table variable as input to a function or stored procedure?

[Herrick]

Very interesting, but no, I did mean a variable of table datatype (new for sql server) Is there a way to pass that to a proc. or function?


------------
Kurt at 7/9/01 8:45:52 PM

By &#34;Table Variable&#34; I am assuming you mean a table column. If that is the case then, yes, you can pass a column to stored procs a couple of ways. Here are two ways that I know of:

1. You can create a cursor and get access to your table data row by row and pass it to an sp. Something like this.

Declare @lColumn1 VariableType
Declare MyCursor Cursor FORWARD_ONLY
For Select Column1 From MyTable
Open MyCursor
Fetch Next From MyCursor Into @lColumn1
While @@Fetch_Status <> -1
Begin
Exec MyStoreProc @lColumn1
Fetch Next From MyCursor Into @lColumn1
End
Close MyCursor

--OR--

2. You could use a UDF (assumeing you have SQL 2000)
CREATE FUNCTION [dbo].[MyFunction] (@pMyColumn VariableType)
RETURNS ReturnVariableType AS
Begin
DECLARE @lRetVal int
Exec MyStoredProc @pMyColumn
-- I don&#39;t know if your stored proc is returning and error
-- But for this example, let just say you&#39;ll always return a 1
Set @lRetVal=1
Return (@lRetVal)
End

Then you can call the UDF like this.
Select dbo.MyFunction(MyColumn) From MyTable

SQL will execute the UDF for every row in the table or for whatever you
specify in the Where clause.


Hope this helps



------------
Herrick Spencer at 7/9/01 1:32:48 PM


Is there any way to use table variable as input to a function or stored procedure?

[Kurt]

Ah, that&#39;s a very different situation. No, you can&#39;t use table variables as input parameters to stored procs or a UDF. For what it&#39;s worth, you can return a table variable in a UDF.

Kurt



------------
Herrick at 7/10/01 10:44:24 AM

Very interesting, but no, I did mean a variable of table datatype (new for sql server) Is there a way to pass that to a proc. or function?


------------
Kurt at 7/9/01 8:45:52 PM

By &#34;Table Variable&#34; I am assuming you mean a table column. If that is the case then, yes, you can pass a column to stored procs a couple of ways. Here are two ways that I know of:

1. You can create a cursor and get access to your table data row by row and pass it to an sp. Something like this.

Declare @lColumn1 VariableType
Declare MyCursor Cursor FORWARD_ONLY
For Select Column1 From MyTable
Open MyCursor
Fetch Next From MyCursor Into @lColumn1
While @@Fetch_Status <> -1
Begin
Exec MyStoreProc @lColumn1
Fetch Next From MyCursor Into @lColumn1
End
Close MyCursor

--OR--

2. You could use a UDF (assumeing you have SQL 2000)
CREATE FUNCTION [dbo].[MyFunction] (@pMyColumn VariableType)
RETURNS ReturnVariableType AS
Begin
DECLARE @lRetVal int
Exec MyStoredProc @pMyColumn
-- I don&#39;t know if your stored proc is returning and error
-- But for this example, let just say you&#39;ll always return a 1
Set @lRetVal=1
Return (@lRetVal)
End

Then you can call the UDF like this.
Select dbo.MyFunction(MyColumn) From MyTable

SQL will execute the UDF for every row in the table or for whatever you
specify in the Where clause.


Hope this helps



------------
Herrick Spencer at 7/9/01 1:32:48 PM


Is there any way to use table variable as input to a function or stored procedure?