U-SQL Procedures
Summary
U-SQL provides the ability to name scripts as procedures and parameterize them. Unlike procedures in other query languages, such as T-SQL or PL/SQL, U-SQL’s procedures do not provide any imperative code-flow constructs such as a for or while loops. Its main differences to U-SQL functions are:
- U-SQL Procedures do not provide return values
- U-SQL Procedures can create and output to files and insert into tables.
- U-SQL Procedures can contain data-definition (DDL) statements to create, alter or drop U-SQL metadata objects (except procedures and functions).
Procedure DDL Statements
U-SQL provides the ability to create or drop procedures:
Syntax
Procedure_DDL_Statement :=
Create_Proc_Statement
| Drop_Proc_Statement.
Procedures can be used inside other procedures and U-SQL scripts.
A list of procedures can be retrieved using the Windows PowerShell cmdlet Get-AzureRmDataLakeAnalyticsCatalogItem. Example command for retrieving procedures from the TestReferenceDB database.
Login-AzureRmAccount;
$DataLakeAnalyticsAccount = "<adla_account>";
Get-AzureRmDataLakeAnalyticsCatalogItem -Account $DataLakeAnalyticsAccount -Path "TestReferenceDB.dbo" -ItemType "Procedure";
(Get-AzureRmDataLakeAnalyticsCatalogItem -Account $DataLakeAnalyticsAccount -Path "TestReferenceDB.dbo" -ItemType "Procedure").Name;