none
How to throw an exception in a U-SQL stored procedure (like SQL Server RAISERROR)

    Question

  • I saw this brief discussion post on Stackoverflow:

    https://stackoverflow.com/questions/51963804/how-to-throw-a-error-or-raise-exception-in-u-sql

    However, I don't really understand the proposed answer...

    I wonder if anyone has done this, and/or if someone has used that proposed answer and is able to post a concrete example of how to create the "C# function" and how to use in the U-SQL procedure.

    Usage examples might be to use one of the FILE.<operation> operators, such as use the FILE.EXISTS() to check dependency before proceeding.

    Thank you in advance!


    Bill Blakey

    Friday, October 19, 2018 8:13 PM

Answers

  • Hi Bill,

    U-SQL’s extensibility model relies heavily on the ability to add custom code from .NET assemblies, so if anything is missing out of the box then you can create a C# Program and generate the assembly and can refer that in your U-SQL Code. So in your case, you can even create a user defined function to use it further in your code like below

    using Microsoft.Analytics.Interfaces;
    using Microsoft.Analytics.Types.Sql;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    namespace USQL_Programmability
    {
        public class CustomFunctions
        {
            public static string Test(int col)
            {
                try{
    //code
    }
        catch
    {
    //throw exception
    }
            }
        }
    }


    Now use this function in your U-SQL code some thing like 

    select Col1,USQL_Programmability.CustomFunctions.Test(col)....


    Cheers,

    Wednesday, October 24, 2018 1:36 AM

All replies

  • Here are some documents to get you started.

    For checking if a file exists:
    https://docs.microsoft.com/en-us/u-sql/functions/metadata/file/file-exists

    For if, else conditions:
    https://docs.microsoft.com/en-us/u-sql/control-of-flow/if-else

    Basically, you would need to declare your file to check, run it through an if-else statement and then use the stackoverflow link to raise and error through U-SQL.

    Let me know if that doesn't make sense.

    Thanks.

    James

    Tuesday, October 23, 2018 11:28 PM
    Moderator
  • Hi Bill,

    U-SQL’s extensibility model relies heavily on the ability to add custom code from .NET assemblies, so if anything is missing out of the box then you can create a C# Program and generate the assembly and can refer that in your U-SQL Code. So in your case, you can even create a user defined function to use it further in your code like below

    using Microsoft.Analytics.Interfaces;
    using Microsoft.Analytics.Types.Sql;
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;

    namespace USQL_Programmability
    {
        public class CustomFunctions
        {
            public static string Test(int col)
            {
                try{
    //code
    }
        catch
    {
    //throw exception
    }
            }
        }
    }


    Now use this function in your U-SQL code some thing like 

    select Col1,USQL_Programmability.CustomFunctions.Test(col)....


    Cheers,

    Wednesday, October 24, 2018 1:36 AM