table Variables as Parameters

Question

table Variables as Parameters

DC-320235

SSC Enthusiast

Points: 169
More actions
June 25, 2009 at 8:26 am

#207473

Perhaps this has been asked, but does anyone know why Microsoft implemented the table datatype for parameters to Procedures, but not to Functions?
I would think the idea of being able to pass a recordset into a (table-valued) function and get a recordset back out would be appealing, but perhaps I am missing something. Or was it simply a matter of a potential feature that got cut to get back on schedule?
EDIT: This was intended to go in the SQL2k8 board. Sorry. :blush:

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

The table datatype can be used as a function parameter, but defining the table type is a prerequisite to using it as a variable datatype.

Here is an example of using a table variable as a function parameter:

CREATE TYPE dbo.SSC_DC_Table AS TABLE

( MyValue INT )

GO

CREATE FUNCTION dbo.SSC_DC_Function

( @MyTable dbo.SSC_DC_Table READONLY )

RETURNS INTEGER

WITH SCHEMABINDING

AS

BEGIN

RETURN ( SELECT SUM(MyValue) FROM @MyTable )

END

GO

DECLARE @MyTable dbo.SSC_DC_Table

INSERT INTO @MyTable (MyValue) VALUES (1),(2) , (3)

SELECT dbo.SSC_DC_Function ( @MyTable )

GO

Clean-up

DROP FUNCTION dbo.SSC_DC_Function;

DROP TYPE dbo.SSC_DC_Table;

go

SQL = Scarcely Qualifies as a Language

DC-320235 SSC Enthusiast Points: 169 More actions · Answer 2

Ah. Okay.

For some reason I was reading, "the nonscalar types, cursor and table, cannot be specified as a parameter data type in either Transact-SQL or CLR functions," to include user-defined data types derived therefrom.

Lowell SSC Guru Points: 323482 More actions · Answer 3

Carl, your example is for SQL 2008, right? I had previously understood that in 2008 you could use the table variables for parameters, and when i tested your code, it of course failed on syntax errors in 2005.

I got mislead by the forum, being 2005 i think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DC-320235 SSC Enthusiast Points: 169 More actions · Answer 4

And now is when I realize that I posted this topic in the wrong part of the forum. Sorry everyone. :blush:

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Lowell - yes , User-Defined Table Type are a new feature of 2008 and does not work on 2005.

"I got mislead by the forum, being 2005 i think." This also confused me but I knew it had to be 2008. I do not have much experience with 2005 but do with 2008. My client has got about 400 SQL Server 2000s that will be upgraded to 2008 over the next year but they only have about 6 SQL Server 2005. The number of 2005 bugs/fixes scared them out of upgrading but the short list for 2008 has given them more confidence about upgrading.

SQL = Scarcely Qualifies as a Language