SQL Server: What, no arrays? Table valued types/parameters, functions and so forth.

When in SQL think tables, an array of integers is just a table with a single integer column. You can define what you might think of as an array type, they are called table valued types.

Some databases will allow a type to be defined of the same type as a table row or column (e.g. Oracle – %ROWTYPE, %TYPE), SQL Server doesn’t, which seems a shame to me at times as I hate re-defining something again in a table valued type, but that is life.

What struck me today was I was trying to return a table valued type from a function, no, it wouldn’t have it, so I had to again re-define the same thing.
This is fine:

CREATE FUNCTION tim(@t NVARCHAR(MAX))
RETURNS @res TABLE (rescol1 INT, rescol2 NVARCHAR(MAX))
AS
BEGIN
	INSERT INTO @res (rescol1, rescol2) VALUES (9, 'hello')
	RETURN
END

But I already have a table valued type defined like so:

CREATE TYPE restype AS TABLE (rescol1 INT, rescol2 NVARCHAR(MAX))

And I can write functions like:

CREATE FUNCTION tim2(@t NVARCHAR(MAX))
RETURNS NVARCHAR(MAX)
AS
BEGIN
	RETURN 'hello'
END

But it won’t allow:

CREATE FUNCTION tim3(@t NVARCHAR(MAX))
RETURNS restype
AS
BEGIN
	DECLARE @res AS restype
	INSERT INTO @res (rescol1, rescol2) VALUES (9, 'hello')
	RETURN @res
END

The error is [Must declare the scalar variable “@res”.]
Any other combination with restype doesn’t work because this way I am declaring a scalar valued function, so it won’t allow restype to be used.

But I want to use restype in my calling T-SQL? Well, you can do this:

DECLARE @vals restype
INSERT INTO @vals
SELECT * FROM tim('t val')

Great, now let’s pass one to a function.

CREATE FUNCTION bill(@tvp restype READONLY)
RETURNS NVARCHAR(MAX)
AS
BEGIN
	DECLARE @r AS NVARCHAR(MAX)
	SELECT TOP 1 @r = CONVERT(NVARCHAR(MAX), rescol1) + rescol2
	FROM @tvp
	RETURN @r
END
GO

DECLARE @vals restype
INSERT INTO @vals SELECT * FROM dbo.tim('t val')
SELECT dbo.bill(@vals)

Notice they have to be READONLY.

Alternatives? Of course, temporary tables spring to mind, but I find them slightly more tricky, you get fatal errors if they already exist, do anything in a loop and kaboom, you’ll have to code that more carefully.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s