Table Variables in Azure Data Warehouse

Question

In a SQL Server database, one can use table variables like this:

declare @table as table (a int)

In an Azure Data Warehouse, that throws an error.

Parse error at line: 1, column: 19: Incorrect syntax near 'table'

In an Azure Data Warehouse, you can use temporary tables:

create table #table (a int)

but not inside functions.

Msg 2772, Level 16, State 1, Line 6 Cannot access temporary tables from within a function.

This document from Microsoft says,

◦Must be declared in two steps (rather than inline): ◾CREATE TYPE my_type AS TABLE ...; , then ◾DECLARE @mytablevariable my_type;.

But when I try this:

create type t as table (a int);
drop type t;

I get this :

Msg 103010, Level 16, State 1, Line 1 Parse error at line: 1, column: 8: Incorrect syntax near 'type'.

My objective is to have a function in an Azure Data Warehouse which uses a temporary table. Is it achievable?

Edit Start Here

Note that I am not looking for other ways to create one specific function. I have actually done that and moved on. I'm a veteran programmer but an Azure Data Warehouse rookie. I want to know if it's possible to incorporate some concept of temporary tables in an Azure Data Warehouse function.

Answer 1

Ok, I believe this is what you are after.

Firstly, this uses a Table Value Function, which are significantly faster than Scalar or Multi-statement Table value Functions.

Secondly, there was no use for a Table Variable, or Temporary Table, just some good odd string manipulation, a bit of maths, and a CTE. Definitely no expensive WHILE loop.

I've tested this against the examples in the link, and they all return the expected values.

USE Sandbox;
GO
CREATE FUNCTION ValidateHealthNumber (@HealthNumber varchar(10))
RETURNS TABLE
AS
RETURN

    WITH Doubles AS(
        SELECT CONVERT(tinyint,SUBSTRING(V.HN,O.P,1)) AS HNDigit,
               CONVERT(tinyint,SUBSTRING(V.HN,O.P,1)) * CASE WHEN O.P % 2 = 0 THEN 1 ELSE 2 END ToAdd
        FROM (VALUES(@HealthNumber)) V(HN)
              CROSS APPLY (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9)) O(P)),
    Parts AS (
        SELECT CONVERT(tinyint,SUBSTRING(CONVERT(varchar(2),ToAdd),1,1)) AS FirstDigit, --We know that the highest value can be 18 (2*9)
               CONVERT(tinyint,SUBSTRING(CONVERT(varchar(2),ToAdd),2,1)) AS SecondDigit --so no need for more than 2 digits.
        FROM Doubles)
    SELECT CASE RIGHT(@HealthNumber, 1) WHEN 10 - RIGHT(SUM(FirstDigit + SecondDigit),1) THEN 1 ELSE 0 END AS IsValid
    FROM Parts;

GO

CREATE TABLE #Sample(HealthNumber varchar(10));
INSERT INTO #Sample
VALUES ('9876543217'), --Sample
       ('5322369835'), --Valid 
       ('7089771195'), --Valid
       ('8108876957'), --Valid
       ('4395667779'), --Valid
       ('6983806917'), --Valid
       ('2790412845'), --not Valid
       ('5762696912'); --not Valid

SELECT *
FROM #Sample S
     CROSS APPLY ValidateHealthNumber(HealthNumber) VHN;
GO
DROP TABLE #Sample
DROP FUNCTION ValidateHealthNumber;

If you don't understand any of this, please do ask.

Answer 2

No you can't. Object can't be created inside User Defined Functions (UDF). Use table variables instead.

If you want yo use user defined type, first create it outside the UDF and use it as a variable type within the UDF.

-- Create the data type
CREATE TYPE TestType AS TABLE 
(
    Id INT NOT NULL,
    Col1 VARCHAR(20) NOT NULL)
GO

-- Create the tabled valued function
CREATE FUNCTION TestFunction
()
RETURNS 
@Results TABLE 
    (Result1 INT, Result2 INT)
AS
BEGIN
    -- Fill the table variable with the rows for your result set
    DECLARE @Var1 TestType;

    RETURN 
END
GO