Add auto completion to SQL Server stored procedures and table-valued functions

[mes-rrivas]

Hello,

In SQL Server, when writing an exec statement or a tvf query, we do not see intellisense/autocomplete for parameters. It also appears there is no intellisense for scalar functions either. That can be a separate issue, but I assume any implementation will combine all routines into one.

Code

I can see in ./sqlit/domains/connections/providers/mssql/adapter.py this section:

   def get_procedures(self, conn: Any, database: str | None = None) -> list[str]:
        """Get stored procedures from SQL Server."""
        cursor = self._get_cursor_for_database(conn, database)
        cursor.execute(
            "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES "
            "WHERE ROUTINE_TYPE = 'PROCEDURE' ORDER BY ROUTINE_NAME"
        )
        return [row[0] for row in cursor.fetchall()]

So the code filters out table valued function and scalar functions from this list. Parameter information can be retrieved from the following query

SELECT *
FROM	INFORMATION_SCHEMA.PARAMETERS

A new suggestion type would be required, I don't think we are using the FUNCTION suggestion type. Perhaps function was intended for tvf and svf calls?

class SuggestionType(Enum):
    """Types of SQL completion suggestions."""

    TABLE = auto()
    COLUMN = auto()
    KEYWORD = auto()
    FUNCTION = auto()
    SCHEMA = auto()
    DATABASE = auto()
    PROCEDURE = auto()
    ALIAS_COLUMN = auto()  # Column for a specific table/alias
    OPERATOR = auto()  # Comparison operators (=, <, >, etc.)
    #PARAMETER = auto #New suggestion type
 

New context matching would be required to trigger the right completions