What is the difference between a stored procedure and a User-Defined Function (UDF)?

The correct answer is A. Stored procedures can execute database operations while UDFs cannot. The key difference is that stored procedures can execute DML statements (INSERT, UPDATE, DELETE), DDL statements (CREATE, DROP), and transaction control - operations that modify database state. UDFs are restricted to computing and returning a value; they cannot execute database…

Snowflake Cloud Data Platform Features and Architecture

Question

Options

AStored procedures can execute database operations while UDFs cannot.
BReturning a value is required in a stored procedure while returning values in a UDF is optional.
CValues returned by a stored procedure can be used directly in a SQL statement while the values
DMultiple stored procedures can be called as part of a single executable statement while a single

How the community answered

(21 responses)

A
90% (19)
B
5% (1)
C
5% (1)

Explanation

The key difference is that stored procedures can execute DML statements (INSERT, UPDATE, DELETE), DDL statements (CREATE, DROP), and transaction control - operations that modify database state. UDFs are restricted to computing and returning a value; they cannot execute database operations or cause side effects. This is why stored procedures are called with the CALL command and cannot be used inline in a SELECT, while UDFs are called inline within SQL statements. Option B is backwards - UDFs must return a value, while stored procedures do not have to. Option C is also backwards - UDF return values can be used in SQL, stored procedure results cannot be used directly in SQL.

Topics

#Stored Procedures#User-Defined Functions (UDFs)#Database Operations#Procedural Logic

Community Discussion

No community discussion yet for this question.

Full COF-C02 Practice