Parameter does not exist in any matching scripts for SQL scripts calling UDF's

[mikkor14]

Hello! I have a DB with a bunch of UDF's and as recommended i am calling them from a script like so: select *from [myDb].GetPrices(@user, @currency)

The function shown in the example is declared in my DB: CREATE function [myDb].[GetPrices] (@user varchar(256), @currency varchar(32))

In my Facil.yaml, i have the script defined like so:

    scripts:
      - include: '**/*.sql'

        result: auto
        paramDto: skip
        voptionIn: false
        voptionOut: false
        recordIfSingleCol: false

      - for: PricesGet.sql
        result: nominal
        params:
          user:
            type: VARCHAR
          currency:
            type: VARCHAR
            nullable: true

But when building my project, i am getting a warning Script rule at index 1 with for = 'PricesGet.sql' has a rule for parameter 'user', but the parameter does not exist in any matching scripts. I am doing something wrong? I have tried declaring the variables before the SELECT statement (declare @user VARCHAR;), but i still get the same warning and cannot use them after generation

[cmeeren]

I need a more complete example. As a start, could you share a minimal example of the script that calls the UDF? This should be a script that produces the error during generation, but does not contain any more code not relevant to the reproduction.

[mikkor14]

I am calling the UDF with the SELECT statement provided above, select *from [myDb].GetPrices(@user, @currency), it is called PricesGet.sql and is inside the project folder. I tested the script by just hardcoding the param values, and it works fine. I tried renaming the variables, and it seemed to help, but now i just get no data from my function. If you want to recreate it yourself, be sure to name all variables the same across the UDF, SQL script and Facil config, i suspect there might be something there

[cmeeren]

Hi, I am happy to help, but I need a minimal reproducible example I can look at to investigate. I have never myself had any problem calling UDFs like that, so likely something is wrong on your end. However, without seeing a minimal script that reproduces the error, I can't tell you what might be wrong.

[cmeeren]

My guess is also that in the process of producing a minimal repro, you will find what is wrong.

[mikkor14]

I think you are misunderstanding, this is my script, i have nothing besides this SELECT statement interacting with the UDF. Do you mean the UDF code? It is just a generic SELECT [data] FROM [core].[PriceDb] WHERE user = @user. I have other UDF's as well, and none of them return any data if their parameter names are matching the ones defined in the SQL script and Facil. Just to be clear, here is all the code that i am currently running: PricesGet.sql: select *from [myDb].GetPrices(@user, @currency)

Facil.yaml:

    scripts:
      - include: '**/*.sql'

        result: auto
        paramDto: skip
        voptionIn: false
        voptionOut: false
        recordIfSingleCol: false

      - for: PricesGet.sql
        result: nominal
        params:
          user:
            type: VARCHAR
          currency:
            type: VARCHAR
            nullable: true

DBAccess.fs:

        let getPrice (connString: string) (user: string option) (currency: string option) =
            PricesGet
                .WithConnection(connString)
                .WithParameters(user, currency)
                .ExecuteAsync()

The code inside the UDF is provided above, but is not relevant since i get no data from any of my UDF's if i am using params in this way

[cmeeren]

I have spent some time trying this out now, but I am unable to reproduce. See the attached minimal solution: UdfRepro.zip

It connects to a local database called UdfRepro that contains no tables and only a single UDF:

CREATE FUNCTION GetPrices(@user VARCHAR(256), @currency VARCHAR(32)) RETURNS INT AS
BEGIN
  RETURN 1
END

This builds fine without warnings or missing parameters. (I have not tested running it, but I understand your primary problem to be at compile/generation time, not runtime.)

If you want me to look further into this, I will, again, need from you such a minimal reproducible example: A .zip file containing only the bare minimum of code needed to reproduce the bug, with everything else removed. I of course also need the definition of any database objects separately, so that I can run them in my local repro database to set it up correctly. These too should, of course, be minimal and contain only what is needed to reproduce the bug.

[mikkor14]

That is indeed quite strange, i will try to recreate it from scratch and see if the problem persists. Thanks for taking the time to reply 👍

[mikkor14]

Alright i got it working now. Turns out that i had to declare parameter length for them to be used properly with my Table-Valued Function. type: VARCHAR(256) helped

[cmeeren]

Great that you solved it! Yes, length must always be specified. (I think if you type it in SQL without a length, it defaults to 1, which is unhelpful and almost never what one wants.)