bug in SQL prompt

Tawfik_Khalifeh_cme

Smart Rename not changing join condition

I've recreated a mock below, the column OldField was changed to NewField, but the join condition is not changed causing the script to fail, this code segment resides inside a stored procedure.

...

FROM dbo.Table1 t1
    LEFT JOIN
    (
        SELECT SUM(t2.[Value]) [Sum],
               t2.NewField
        FROM dbo.t2
        GROUP BY t2.NewField
    ) ext1

        ON ext1.OldField = t1.NewField

Find Invalid Objects failing to spot some cases

- not spotting incorrect procedure param list when invoked using exec

EXEC dbo.usp_dosomething @param1 = 1,
                         @wrong_param_name = 2,
                         @non_existent_param_name = 3,
                         ...

- not spotting mismatching columns when doing an INSERT INTO EXEC, as in stored procedure with select statement hasving 4 columns while the table the user is trying to insert into has 3 columns (or same number of columns but incompatible types)

INSERT INTO table
(
    Col1,
    Col2,
    Col3,
    ...
)
EXEC dbo.usp_dosomething @param1 = 1,
                         @param2 = 2,
                         @etc = 3,
                         ...

Alex B

Hi @Tawfik_Khalifeh_cme,

Unfortunately, I think you are mistaking the intended purpose of those functionalities; the features are not designed to perform the actions you are looking for them to do.

The Smart Rename functionality is specifically for Tables, Views, Stored procedures and Functions - see this page.  It is not for column names.  There is also the F2 invoked rename scripted objects which is for variables and aliases (and a few other occurrences, including renaming temporary table columns, but not table columns) - see this page.  If the column you have renamed no longer exists in the table then the parser will show a red squiggle under the OldField name indicating it is an invalid column name.

For Find Invalid Objects, objects are listed as invalid when they can't be compiled or if they can be compiled but can't be executed - see this page.  This does not include calling the object incorrectly or incorrectly inserting values into a table.

The first issue should be picked up by the parser and will display as a red squiggled underline beneath either the parameters or the name of the stored procedure itself (something to the effect of the procedure has too many arguments).

The last example you mention doesn't actually raise anything until it is run so for this and all of the above, if you would like to see them added as features to SQL Prompt, you should see if there is an existing suggestion on the SQL Prompt Uservoice forum and vote on it or raise a new suggestion for others to vote on.

Kind regards,
Alex

Marko68

What??? The Smart rename doc clearly states:

SQL Prompt can create a script that allows you to rename objects in your database without breaking dependencies. You can rename the following:

• Tables (including columns)
• Views (including columns)
• Stored procedures (including parameters)
• Functions (including parameters)

INCLUDING COLUMNS  - so yes, renaming columns IS in the code - and it doesn't work properly!

Alex B

Hi @Marko68,

You're absolutely right.... I just must not have looked past the object types so my apologies for that to everyone!

I've updated issue SP-7597 with a reference here, - @Marko68 if you have a different example from the above please update and let me know so I can add it to the issue.

Kind regards,
Alex