Missing function in object browser

Hi Ansgar and everybody,

Great work!

We are using SQL Server 2008. I create following function:

CREATE FUNCTION MyTestFunc () RETURNS @t table (id int) AS
BEGIN
INSERT @t SELECT 100
RETURN
END;

After creation I can call the table successfully:

SELECT * FROM MyTestFunc();

Above query returns id / 100, which is correct.

Now the problem is, the function "MyTestFunc" does not show up in the object browser in HeidiSQL (version 8.3 build 4822). Therefore I cannot use the GUI for altering the function.

Can this be fixed, please? (there are a lot of legacy functions of this kind in our DB and this issue does not allow us to use HeidiSQL)

kind regards,
peter

Does that MyTestFunc show up in the results of the following query?

SELECT *, SCHEMA_NAME("schema_id") AS 'schema' FROM "!!YOURSCHEMA!!"."sys"."objects" WHERE "type" IN ('P', 'U', 'V', 'TR', 'FN');

Please replace "!!YOURSCHEMA!!" with your schema name.

If not so, please remove the WHERE clause and watch out for the value of that function in its "type" and "type_desc" columns.

It does not show up using the given "type" filter.
Having removed the WHERE clause, returns MyTestFunc with type = "TF" and type_desc = "SQL_TABLE_VALUED_FUNCTION".

r4828 includes these table valued functions.

Great, the obj browser lists the func now! :)

Unfortunately I still cannot edit the func. When I change 100 to 200 (in above example) and click on [Save] button, it throws an error. Here the log:

SELECT ROUTINE_NAME FROM "information_schema"."ROUTINES" WHERE ROUTINE_SCHEMA = '!!MYSCHEMA!!' AND ROUTINE_TYPE = 'FUNCTION';
CREATE FUNCTION "HeidiSQL_temproutine_1"()
RETURNS @t
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
table (id int) AS
BEGIN
INSERT @t SELECT 200
RETURN
END;
/* SQL Error (1087): Incorrect syntax near 'LANGUAGE'
Must declare the table variable "@t". */

It seems Heidi adds some code in the CREATE statement which results in a invalid syntax for this type of function.

Hi Ansgar,

shall I start a new thread regarding my last post here, as it does not really refers to the current thread's title?

Additionally, how can I help to solve such issues? I sometimes think that Heidi is more built for MySQL/Maria only and things not fully working for other DBs such as MS SQL. Would it help if I would provide the correct SQL working for MS SQL in such cases?

Yes, HeidiSQL is still more a MySQL/MariaDB client than it is for MSSQL or PostgreSQL. The usage statistics on the start page shows the reason for that. But I guess as a free software, HeidiSQL is still usefull for MSSQL and PostgreSQL users in simple use cases.