Share via

Calling a Procedure (U-SQL)

  • Article

Summary

A procedure can be called inside a script or procedure and will be inlined into the execution graph before executing it. The invocation is similar to a table-valued function invocation with the difference that no rowset variable is being assigned. The compiler does not allow more than 50 nested procedure calls to prevent stack overflow; please make sure that procedures are called with less nesting.

Syntax

Procedure_Call :=                                                                                        
    Identifier '(' [Argument_List] ')'.

Argument_List :=  
    Argument {',' Argument}.
  
Argument :=  
    argument_expression  
|   'DEFAULT'.

Remarks

  • Identifier
    Specifies the name of the called procedure. If the procedure name is a fully-specified three-part name, the procedure in the specified database and schema will be called. If the name is a two-part name, the procedure will be called in the current database context and specified schema. If the name is a simple identifier, then the procedure will be called in the current database and schema context.

    If the procedure is not found or the user has no right to call it, an error is raised.

  • Argument_List
    A procedure may take parameters for which values need to be provided. Unlike C# functions, one does not specify the parameter name when passing the argument values. Instead the values are assigned positionally to the parameters. One can either provide values using an expression or one can use the DEFAULT keyword to have the parameter’s default value chosen. Any expression passed to a parameter will be inlined in the function’s body. This means that non-deterministic expressions are not made deterministic by U-SQL; they will still be non-deterministic if they are invoked more than once by the final script unless they are known to be non-deterministic to U-SQL.

Examples

// This example will call the procedure myFirstStoredProc:
TestReferenceDB.dbo.myFirstStoredProc();


// This example will call the procedure myStoredProcWithParameters:
TestReferenceDB.dbo.myStoredProcWithParameters  
(  
    200,  
    DateTime.Parse("12/11/2013")   
); 


// These two examples will call the procedure getPeople:
// Using default value for @filePath
TestReferenceDB.dbo.getPeople((new SQL.ARRAY<int>{100, 300, 800}), DEFAULT); 

// Specifying value for @filePath
TestReferenceDB.dbo.getPeople((new SQL.ARRAY<int>{100, 300, 800}), "/Output/ReferenceGuide/DDL/Procedure/getPeople2.csv");

Additional Example

See Also