Bug: table-valued function output columns are prompted in the parameter list

bstrautinbstrautin Posts: 8 Bronze 2
edited March 12, 2020 3:52PM in SQL Prompt
SQL Prompt suggests the output columns of table-valued functions as as parameter values to the function itself.

For example, with this code, place cursor after "dbo.foo(x" and hit Ctrl-Space. SQL Prompt will suggest "f.x", but it should not, because that will produce dbo.foo(f.x) x, which is invalid.

go<br>create function dbo.foo (@x int)<br>returns @foo table(x int)<br>as<br>begin<br>&nbsp;&nbsp; &nbsp;insert @foo(x) values (1)<br>&nbsp;&nbsp; &nbsp;return<br>end<br>go<br><br>select *<br>from dbo.foo(x) f


Tagged:
· Share on Twitter

Answers

  • bstrautinbstrautin Posts: 8 Bronze 2
    Also, there is apparently a bug in the forum software; the code is present when I edit my post, but it only displays "go"

    Code below (not in a code block):

    go
    create function dbo.foo (@x int)
    returns @foo table(x int)
    as
    begin
        insert @foo(x) values (1)
        return
    end
    go

    select *
    from dbo.foo(x) f

    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @bstrautin,

    I think the issue with the code is two things - first that it's interpreting the variables with the @ symbol as users and linking to a user profile for them and second that your line endings may be something it's not expecting.  I have removed the @ links and deleted and replaced each line ending (by pressing delete then enter for each) it works for me.
    go
create function dbo.foo (@x&nbsp;int)
returns&nbsp;@foo&nbsp;table(x int)
as
begin
&nbsp;&nbsp;&nbsp; insert&nbsp;@foo(x)&nbsp;values (1)
&nbsp;&nbsp;&nbsp; return
end
go

select *
from dbo.foo(x) f

    As for your issue, when I put the cursor after the x in this code and press Ctrl+Space it qualifies the x with [f] which it looks like it should.  If I don't have the characters afterwards `) f` it suggests XLOCK and nothing else happens.

    This is on 10.1.0 on SSMS 18.4.

    Am I misunderstanding your issue?

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • bstrautinbstrautin Posts: 8 Bronze 2
    edited March 12, 2020 3:54PM
    It should NOT qualify the x with [f], because [f] refers to the function being called, and it's impossible to use the function's output as its own input parameters.

    P.S. tried the delete+enter trick, but no luck.
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @bstrautin,

    Righto I see what you are saying now.

    Just to confirm,  as I couldn't see it happen in other circumstances, this is only when the entire script is written out and you place the cursor in the specified position and ask for the suggestion right?

    I didn't see it occur when normally formatting the entire script or in other circumstances, so just want to make sure of the specific case.

    And, in that vein, what is the use-case here? What should it be doing (or just nothing at all)?

    Kind regards,
    Alex

    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @bstrautin,

    Thanks for your patience with this issue.

    The development team have let me know that, unfortunately, they are not going to address this bug at the moment.

    When reviewing bugs the development team take into account the impact it has and the number of users affected and unfortunately we are unable to fix all bugs and have to prioritize fixing the ones that cause the greatest impact.

    Thank you for for reporting it to us and your understanding!

    Kind regards,

    Alex

    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • bstrautinbstrautin Posts: 8 Bronze 2
    edited May 5, 2020 3:57PM
    The use case is writing a query with a CROSS APPLY where the function being applied outputs a column with the same name as a table that it's being applied against, e.g. something like:

    <br>select p.partno, p.description, c.partno<br><br><div>from Parts p</div><div><br></div><div>cross apply dbo.get_children([cursor here, wanting "p.partno", but typing "pa(tab)" gives me "gc.partno"]) gc</div><div><br></div><div></div>

    It is super-annoying that gc's partno is prompted in its own parameter list, above p's.

    The original example just demonstrates the problem minimally.

    Also, the editor continues to not post code properly, so here it is again, not in a code block:

    select p.partno, p.description, c.partno
    from Parts p
    cross apply dbo.get_children([cursor here, wanting "p.partno", but typing "pa(tab)" gives me "gc.partno"]) gc

    · Share on Twitter
  • Alex BAlex B Posts: 1,158 Diamond 4
    Hi @bstrautin,

    Thank you for that example - I have passed it on to see if it changed the verdict, but unfortunately it's still not currently going to be looked at.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
Sign In or Register to comment.