U-SQL Procedures

  • Article

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:

  1. U-SQL Procedures do not provide return values
  2. U-SQL Procedures can create and output to files and insert into tables.
  3. 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;

See Also