Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Table Variables in Azure Data Warehouse

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.

like image 584
Dan Bracuk Avatar asked Jan 17 '18 20:01

Dan Bracuk


People also ask

What is a table variable?

Table variable is a type of local variable that used to store data temporarily, similar to the temp table in SQL Server. Tempdb database is used to store table variables. To declare a table variable, start the DECLARE statement. The name of table variable must start with at(@) sign.

What is difference between table variable and temp table?

They reside in the tempdb database much like local SQL Server temp tables. Also like local SQL temp tables, table variables are accessible only within the session that created them. However, unlike SQL temp tables the table variable is only accessible within the current batch.

Can we use table variable in stored procedure?

However, you can create the table variable and perform all processing inside the EXEC statement or the sp_executesql stored procedure because then the table variables local scope is in the EXEC statement or the sp_executesql stored procedure.


2 Answers

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.

like image 110
Larnu Avatar answered Oct 20 '22 21:10

Larnu


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
like image 36
Chamika Goonetilaka Avatar answered Oct 20 '22 21:10

Chamika Goonetilaka