|
Home > Archive > MS SQL Server > June 2005 > table variables as output parm for SP ?
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
table variables as output parm for SP ?
|
|
| Regan Galbraith via SQLMonster.com 2005-06-15, 3:23 am |
| Can table variable be used as output or input variables for stored procedures?
I can find anything in BOL, or Ken Henderson's "Guru's Guide to .." books, or
google (so far).
CREATE PROCEDURE [dbo]. UP_ExtractPostCard_C
ard
@PostCardExtractCard
ResultSet TABLE
(& #91;ID] INTEGER ID
ENTITY(1,1),
& #91;DataLine] varch
ar(110))
gives a syntax error near TABLE, but the table declaration is fine:
DECLARE @PostCardExt
ractCardResultSet TA
BLE
(& #91;ID] INTEGER ID
ENTITY(1,1),
& #91;DataLine] varch
ar(110))
Works fine..
Anyone?
--
Message posted via http://www.webservertalk.com
| |
| Uri Dimant 2005-06-15, 3:23 am |
| No, you should consider using Table-Valued UDFs
USE Northwind
GO
CREATE FUNCTION dbo.get_cust_orders
(
@custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VIN
ET') AS C
"Regan Galbraith via webservertalk.com" <forum@nospam.webservertalk.com> wrote in
message news:4FDE49EEAE660@w
ebservertalk.com...
> Can table variable be used as output or input variables for stored
procedures?
> I can find anything in BOL, or Ken Henderson's "Guru's Guide to .." books,
or
> google (so far).
>
> CREATE PROCEDURE [dbo]. UP_ExtractPostCard_C
ard
> @PostCardExtractCard
ResultSet TABLE
> ([ID] INTEGER IDENTITY(1,1),
> [DataLine] varchar(110))
>
> gives a syntax error near TABLE, but the table declaration is fine:
>
> DECLARE @PostCardExtractCard
ResultSet TABLE
> ([ID] INTEGER IDENTITY(1,1),
> [DataLine] varchar(110))
>
> Works fine..
>
> Anyone?
>
> --
> Message posted via http://www.webservertalk.com
| |
| Regan Galbraith via SQLMonster.com 2005-06-15, 3:23 am |
| Thanks Uri...
I did see the UDF usage of Table variables. It is funny that BOL etc. doesn't
state that one cannot use table variables as parameter's, but that seems to
be the case.
So basically, I'll have a UDF that generates my resultset, and puts it into
the table variable. The code calling that UDF will have a table variable that
receives the data, and then I do whatever with ti, after that.
--
Message posted via http://www.webservertalk.com
|
|
|
|
|