Create Insert function

Question

Can anyone please tell me where i'm wrong at

CREATE OR REPLACE FUNCTION ttestt (url varchar(255)) RETURNS VOID AS
BEGIN
    DECLARE @IMAGE_ID INT
    INSERT INTO images(url,ajoute_par) VALUES ("http://www.example.com","3")
    SET @IMAGE_ID = SCOPE_IDENTITY() 
    INSERT INTO Dossier(nom, etat, dossierImage) VALUES ('NameTest', 1, @IMAGE_ID)
END

I'm getting this error :

    #1064 - You have an error in your SQL syntax; check the manual that corresponds 
    to your MySQL server version for the right syntax to use near 'FUNCTION ttestt (url varchar(255)) RETURNS VOID AS
    BEGIN
        DECLARE @IMAGE_ID' at line 1

Can you give us the error, or tell us what variant of SQL you're using? — PausePause, Jul 20 '18 at 20:27
If you are using SQL Server, functions cannot return `void`, functions cannot modify tables, and you should be using the `output` clause to return ids. — Gordon Linoff, Jul 20 '18 at 20:27
@GordonLinoff yes i'm using sql server.. I don't want to use the SCOPE_IDENTITY(), i want to insert at the same time into the two tables — bxxb, Jul 20 '18 at 20:31
@koreangirl . . . `scope_identity()` isn't even a MySQL function. — Gordon Linoff, Jul 21 '18 at 02:01

PausePause · Answer 1 · 2018-07-20T20:43:23.300

Based on your comments and the script you've provided the error here is that you don't have a return statement in your function. Functions in SQL must have some form of return statement. If you don't want to return any value use a stored procedure instead.

Like @GordonLinoff said, functions cannot be used to alter tables. Again, more reasons to use a stored procedure.

Regarding the SCOPE_IDENTITY(), here are some alternatives to look into

Create Insert function

1 Answers1