Variable tables in Azure Data Warehouse

Question

You can use table variables in a SQL Server database as follows:

declare @table as table (a int) 

In the Azure Data Warehouse that is causing the error.

Parsing error in row: 1, column: 19: incorrect syntax near table

In 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 Unable to access temporary tables from inside the function.

This document from Microsoft says

◦ Must be declared in two steps (and not in a line): ◾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 Parsing error in line: 1, Column: 8: Invalid syntax next to "type".

My goal is to have a function in the Azure Data Warehouse that uses a temporary table. Is it possible?

Change start here

Please note that I am not looking for other ways to create one specific function. I really did it and continued. I am a veteran programmer but new to Novell Azure Data Warehouse. I want to know if some concept of temporary tables can be included in the Azure Data Warehouse function.

Answer 1

No, you can’t. An object cannot be created inside user-defined functions (UDF). Use table variables instead.

If you want to use a user-defined type, first create it outside of UDF and use it as a variable type inside 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 

Answer 2

Well, I believe that this is what you need.

First, it uses a table value function, which is significantly faster than a Scalar or Multi-Statement table value function.

Secondly, for the Variable or Temporary Table, there was no need to use only some good odd row manipulations, a bit of math and CTE. Definitely not an expensive WHILE loop.

I tested this against the examples in the link, and they all returned 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,OP,1)) AS HNDigit, CONVERT(tinyint,SUBSTRING(V.HN,OP,1)) * CASE WHEN OP % 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 do not understand this, please ask.