https://docs.snowflake.com/en/developer-guide/stored-procedures-vs-udfs

Stored Procedure

Generally to perform administrative operations by executing SQL statements.(DDL or DML)

• Being called independently and with one statement
• You call a stored procedure as an independent statement. e.g. CALL MyStoredProcedure_1(argument_1);
• A single SQL statement can call only one stored procedure.
• You can call the stored procedure inside another stored procedure
• stored procedure is limited to return only one value to its caller.

User-defined function (UDF)

Calculate and return a value.

• A function always returns a value explicitly by specifying an expression.
• Your output needs to include a value for every input row or every group. e.g. SELECT MyFunction(col1) FROM table1;
• In a UDF, you can use SQL to execute queries only (not DML or DDL statements).