Calling SQLServer stored functions (not procs)

[hhhapz]

Hey! I was wondering if it is possible to call functions and get the return value using this driver?

According to https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/execute-user-defined-functions?view=sql-server-ver15, you must assign the result of the function to a variable. I have tried many different things, however nothing has worked out for me so far:

    const sqlstr = `@p1 = EXEC test_schema.my_func_with_0_params `
    var res int
    if _, err := db.ExecContext(ctx, sqlstr, sql.Out{Dest: &res}); err != nil {
        return 0, logerror(err)
    }
    return res, nil
}

[tc-hib]

Hello, I was about to make a PR about this. There's a bug in this driver, the output keyword is missing in RPC calls. Could be fixed soon, as it is rather simple. If nobody does before I can get back to my computer I'll post the PR.

Edit: looks like I'm talking about another issue :) You're not using the right syntax, I think you simply have to remove @p1 = EXEC and pass a ReturnStatus instead of an int. Please read the Readme. The driver cannot even know you're using a parameter named @p1 since you didn't use sql.Named.

[hhhapz]

Hey @tc-hib, thanks for your response. However, I am using stored functions, not procs! And the return type of the function is incidentally an integer, not trying to get the return status (I can't find anything about what the purpose of this is, even in the readme it is quite vague about what the actual purpose is). If I try to use syntax similar to stored procedures to call the function, I just get an error no rows in result set.

Here is the function I am attempting to call:

[tc-hib]

Alright so you actually need that bug fix I was talking about. I'll try to send a PR soon.

[tc-hib]

You'll need #673 for this to work.

Here is the syntax, from your first post:

const sqlstr = `SELECT @p1 = test_schema.my_func_with_0_params()`
var res int
if _, err := db.ExecContext(ctx, sqlstr, sql.Named("p1", sql.Out{Dest: &res})); err != nil {
    return 0, logerror(err)
}
return res, nil

[hhhapz]

Thanks for making the PR :)

[dzsquared]

As a workaround in the meantime - while the TSQL syntax example shows a scalar function being placed into a variable, user-defined functions can be included in SELECT statements to return values in more traditional result sets. In your example, the function can be called as a column within a select statement:

select test_schema.a_func_0_in() as udfColumn

Effectively giving a result set of 1 row, 1 column, with the value 10.

[hhhapz]

Cheers!!

On Mon, Jul 12, 2021 at 10:26 AM Drew Skwiers-Koballa < @.***> wrote:

As a workaround in the meantime - while the TSQL syntax example shows a scalar function being placed into a variable, user-defined functions can be included in SELECT statements to return values in more traditional result sets. In your example, the function can be called as a column within a select statement:

select test_schema.a_func_0_in() as udfColumn

Effectively giving a result set of 1 row, 1 column, with the value 10.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/denisenkom/go-mssqldb/issues/672#issuecomment-877941388, or unsubscribe https://github.com/notifications/unsubscribe-auth/AHBKDOGBBOJZ2Q5PNCJRWPTTXJOG7ANCNFSM5AFDXYEQ .