Table Variables in Azure Data Warehouse

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.

User defined types are not supported in Azure Data Warehouse. Can you just use a CTE in the function instead?

I can work around the problem if I have to, but I'd rather not. The function I am working on incorporates the logic presented here in Code Review. codereview.stackexchange.com/questions/185329/…

What are you actually trying to get your function to do here? What was the SQL you wrote initially that didn't work?

The link to Code Review shows the sql I initially wrote, but I wrote it against a database. When I tried to put it into a data warehouse function, I failed.

That SQL you've linked to has 3 datasets that are returned. A Function can only return 1 dataset/value. Which dataset are you looking to return?

2 Answers 2

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.