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.