Table of contents
TOC
Collapse the table of content
Expand the table of content

CREATE PROCEDURE (U-SQL)

Michael Rys|Last Updated: 5/15/2018
|
4 Contributors

U-SQL creates a procedure with the CREATE PROCEDURE statement. For information on calling a procedure, see Calling a Procedure (U-SQL)

Syntax
Create_Proc_Statement :=                                                                                 
    'CREATE' 'PROCEDURE' ['IF' 'NOT' 'EXISTS'] Identifier   
    '(' [Parameter_List] ')'  
    ['AS']  
    'BEGIN'  
    Proc_Statement_List  
    'END'.

Semantics of Syntax Elements

This statement creates the procedure with the specified identifier and parameters based on the provided statement list.

Note that setting the context inside the procedure body with a USE statement, declaring variables or referencing assembly statements are only affecting the static context of the procedure’s body and will not be visible in the calling context or the static context of the definition of an object called within (e.g., the script or another procedure).

The procedure’s own static context is not affected by the calling environment’s static context. E.g., a USE DATABASE statement or a variable declaration in the script that is calling the procedure is not affecting the procedure’s default static database context and is not visible inside the procedure body.

Assemblies referenced in a procedure body will however be visible in the calling environment’s dynamic context at runtime and will be visible in any of the called contexts and the procedure’s own dynamic context. In addition, the procedure’s dynamic context will inherit the loaded assemblies from the calling environment.

Deploying a resource inside a procedure will become visible to the whole script at runtime, similar to referencing an assembly and any resources deployed by the calling environment will be visible in the procedure’s dynamic context.

Examples

  • The examples can be executed in Visual Studio with the Azure Data Lake Tools plug-in.
  • The scripts can be executed locally. An Azure subscription and Azure Data Lake Analytics account is not needed when executed locally.
  • The examples below utilize the table dbo.somePeople as defined below.

Dataset

// Create database
CREATE DATABASE IF NOT EXISTS TestReferenceDB; 
USE DATABASE TestReferenceDB; 

// Create table
DROP TABLE IF EXISTS dbo.somePeople;
CREATE TABLE dbo.somePeople
(
    EmpID int,
    EmpName string,
    DeptID int,
    Salary int?,
    StartDate DateTime,
    PhoneNumbers string,
    INDEX clx_EmpID CLUSTERED(EmpID ASC)
    DISTRIBUTED BY HASH (EmpID) 
);

// Populate table
INSERT INTO dbo.somePeople
VALUES
(1, "Noah",   100, (int?)10000, new DateTime(2012,05,31), "cell:030-0074321,office:030-0076545"),
(2, "Sophia", 100, (int?)15000, new DateTime(2012,03,19), "cell:(5) 555-4729,office:(5) 555-3745"),
(3, "Liam",   100, (int?)30000, new DateTime(2014,09,14), ""),
(6, "Emma",   200, (int?)8000,  new DateTime(2014,03,08), (string)null),
(7, "Jacob",  200, (int?)8000,  new DateTime(2014,09,02), "cell:(5) 555-3932"),
(8, "Olivia", 200, (int?)8000,  new DateTime(2013,12,11), "cell:88.60.15.31,office:88.60.15.32"),
(9, "Mason",  300, (int?)50000, new DateTime(2016,01,01), "cell:(91) 555 22 82,office:(91) 555 91 99, home:(425) 555-2819"),
(10, "Ava",   400, (int?)15000, new DateTime(2014,09,14), "cell:91.24.45.40,office:91.24.45.41"),
(11, "Ethan", 400, (int?)9000,  new DateTime(2015,08,22), "cell:(604) 555-4729,office:(604) 555-3745");

/* optional
OUTPUT TestReferenceDB.dbo.somePeople
TO "/Output/ReferenceGuide/DDL/Types/somePeople.txt"
ORDER BY EmpID ASC
USING Outputters.Tsv();
*/

myFirstStoredProc
The following example creates a stored procedure called myFirstStoredProc that outputs the content of somePeople to a CSV file.

DROP PROCEDURE IF EXISTS TestReferenceDB.dbo.myFirstStoredProc;
CREATE PROCEDURE TestReferenceDB.dbo.myFirstStoredProc()  
AS  
BEGIN 
    @result =  
        SELECT *  
        FROM TestReferenceDB.dbo.somePeople;  

    OUTPUT @result  
    TO "/Output/ReferenceGuide/DDL/Procedure/myFirstStoredProc.csv"  
    USING Outputters.Csv();    
END;

myStoredProcWithParameters
The following example creates a stored procedure called myStoredProcWithParameters that outputs the content of somePeople that matches the city and date passed to the procedure.

DROP PROCEDURE IF EXISTS TestReferenceDB.dbo.myStoredProcWithParameters;
CREATE PROCEDURE TestReferenceDB.dbo.myStoredProcWithParameters(@deptID int, @startDate DateTime)  
AS  
BEGIN 
@result =  
    SELECT *  
    FROM TestReferenceDB.dbo.somePeople
    WHERE DeptID == @deptID  
    AND StartDate == @startDate;  

    OUTPUT @result  
    TO "/Output/ReferenceGuide/DDL/Procedure/myStoredProcWithParameters.csv"  
    USING Outputters.Csv();  
END;

getPeople
The following example creates a stored procedure called getPeople that outputs the content of somePeople that matches the array of DeptID values passed to the procedure. In addition, you can pass the location of the output to the @filePath parameter. A default value for @filePath is assigned.

DROP PROCEDURE IF EXISTS TestReferenceDB.dbo.getPeople;
CREATE PROCEDURE TestReferenceDB.dbo.getPeople(
    @deptIDarray SQL.ARRAY<int>, 
    @filePath string = "/Output/ReferenceGuide/DDL/Procedure/getPeople.csv") 
AS  
BEGIN 
    @result =
        SELECT *
        FROM TestReferenceDB.dbo.somePeople
        WHERE @deptIDarray.Contains(DeptID);

    OUTPUT @result
    TO @filePath
    USING Outputters.Csv();
END;

Additional Examples

See Also

© 2018 Microsoft