none
USQL - use a stored procedure to call USE SCHEMA

    Question

  • For some of my USQL jobs, I want to use a specific database schema dependent on a scalular variable representing a string @siteId. 

    My procedure looks like this for now:

    CREATE PROCEDURE useSchema(@siteId string) AS
    BEGIN (
      IF (@siteId == "2ddad552-6353-4202-a6c9-96a567b85b34") THEN
        USE SCHEMA JeanRabel;
      ELSEIF (@siteId == "9b1b16b2-8d53-4b9a-8098-ce54f5e1959e") THEN
        USE SCHEMA MoleSaintNicolas;
      ELSEIF @siteId == "fdc87138-d937-4a91-8769-fc2a763ba284" THEN
        USE SCHEMA Staging;
      END;
    ) END;

    In my script I am using it like this:

    DECLARE @siteId string = "2ddad552-6353-4202-a6c9-96a567b85b34";
    
    master.dbo.useSchema(@siteId);
    
    DROP TABLE IF EXISTS Account;
    CREATE TABLE Account(
      INDEX a_idx CLUSTERED (id ASC)
      DISTRIBUTED BY HASH (id)
    ) AS SELECT * FROM master.dbo.Accounts(@siteId) AS a;

    However the Account table is still being created in master.dbo instead of where I want it, master.JeanRabel. It's the same problem even if I inline the procedure creation into the script. How do I use a procedure to switch database schemas?

    Can anyone help?

    Thursday, February 1, 2018 1:00 AM

Answers

  • Hi there,

    The context appears to be lost after the call to the procedure.  It seems as though you need to set the database context directly in the script rather than a child script. And USE statement can't use a variable.

    Depending on how you are orchestrating the job, perhaps you can do this outside the stored procedure as part of the application calling the script?

    cheers,

    Andrew


    Andrew Sears

    Thursday, February 1, 2018 9:41 PM

All replies

  • Hi there,

    The context appears to be lost after the call to the procedure.  It seems as though you need to set the database context directly in the script rather than a child script. And USE statement can't use a variable.

    Depending on how you are orchestrating the job, perhaps you can do this outside the stored procedure as part of the application calling the script?

    cheers,

    Andrew


    Andrew Sears

    Thursday, February 1, 2018 9:41 PM
  • Andrew's answer is correct. U-SQL's Stored Procs and Functions do not leak their contexts. So you either need to move all of your DDL into the stored proc or move the schema out into the script.

    I also suggest that you use DECLARE EXTERNAL @siteId. That will give you the option to set the parameter of the script in ADF and other parameter models.


    Michael Rys

    Monday, February 5, 2018 6:59 AM
    Moderator
  • See DECLARE Variables (U-SQL) for DECLARE EXTERNAL examples.

    Monday, February 5, 2018 5:21 PM