What is the SQL function?

SQL functions are methods which accept parameters and perform the action, such as a complex calculation and return the result of that action as a value. the return value can either be a single scalar value, result set. We can’t use a function to Insert, Update, Delete records in the database table(s).

Available types of functions in SQL.

  1. Scalar Functions
  2. Table-Valued Functions
  3. System Function

What is the stored procedure?

Stored Procedures are pre-compiled objects which are compiled for the first time and its compiled format is saved which executes whenever it is called.

Difference between SQL function and Stored procedure?

  1. A function must return a value but in Stored Procedure, it is optional( Procedure can return zero or n values).
  2. Functions can have only input parameters for it whereas Procedures can have input/output parameters.
  3. Functions can be called from Procedure whereas Procedures cannot be called from Function.
  4.  The procedure allows SELECT as well as Insert, Update, Delete statement where Function allows only SELECT statement.
  5. Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
  6. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
  7. Inline Function can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.
  8. We can handle Exception by try-catch block in a Procedure but not in functions.
  9. We can perform Transaction Management in Procedure whereas we can’t go in Function.

Can we call the Stored procedure inside SQL function?

No, You cannot execute a stored procedure inside a function, because a function is not allowed to modify database state, and stored procedures are allowed to modify database state.

Can we call a function inside the Stored procedure?

Yes, We can call functions inside the stored procedures.

Is Row_Number generate duplicate Id?

No, ROW_NUMBER  Returns a unique number for each row starting with 1. For rows that have duplicate values, numbers are arbitrarily assigned.

what is Scope identity in SQL?

Scope_Identity returns the last identity value generated in the current scope (i.e. stored procedure, trigger, function, etc).

What is CTE in SQL?

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.


About The Author

Deependra Kushwah
Deependra kushwah is a member of the fastest growing bloggers community "betechnical", Author, Youtuber, and hardcore Coder. I love writing code in different languages, I also write blogs on tech tutorials, gadgets review and also post some technical videos on youtube on many topics.

This site uses Akismet to reduce spam. Learn how your comment data is processed.