Here are the key differences between SP and function basing on syntax and accessibility Stored procedures: Stored procedures are used to perform tranaactions. Stored proecedures can have IN, INOUT and OUT variables. All the database operations insert,update and delete can be performed Exception Handling can be done. Need not return any value. We cannot call procedures from select or having and where clauses. Function: A function only supports IN parameters. No output parameters are supported. Function can be called from select clause. Functions can't call stored procedures. whereas stored procedures can call functions. Functions should always return some value. Functions cannot perform all database operations in it. Only select is allowed. Functions do not provide exception handling. No transactions
@theengineersdesk7930
Жыл бұрын
Well explained
@MarekKnapek
Жыл бұрын
So functions are more limited / more specialized, therefore could be more performant than procedures. Procedures are more flexible / could do more things at cost of less possible optimalizations / pre-compilation by DB engine. Did I understand that correctly? Each is better for different use case.
@mileselam641
Жыл бұрын
These definitions will work for MS SQL Server. They will NOT work for other engines like PostgreSQL. For example, in Postgres functions can have IN, OUT, and INOUT parameters as well as return single values or sets of values. In Postgres the main difference is that stored procedures never return a value and can have multiple transactions within the body, not just one. The SQL world is both vast and varied.
@RaviShankar-xy6zh
Жыл бұрын
@@mileselam641 I am not sure about PostgreSql. However as you said sql is vast and varied but with respect to every RDBMS as SQL is ANSI standard. The differences I posted are most generic. Whenever interview asks this question he expects it in generic way. Just like No RDBMS satisifies all the 13 EF codd rules. No database may satisfy all the differences between SP and functions. In postgresql select abc will result in abc. However till oracle 21 select abc from dual; (a dummy table is required) . Way of writing sql is different in various db as you said.
@mileselam641
Жыл бұрын
@@RaviShankar-xy6zh Then you may be surprised to learn that of all the relational database management systems out there, Postgres is in fact the one most closely adhering to the ANSI/ISO SQL specs. MS SQL Server was derived from Sybase, using T-SQL, a dialect notably different from all others. Only MS SQL Server restricts functions from having OUT or INOUT parameters. It is NOT a restriction from the standard. Oracle, Postgres, and others can do it just fine. The SQL standard also does not mandate only SELECT statements in a function.
@abdulhai_007
7 ай бұрын
Wer is d difference? Give the difference or change ur question title to 'how to create SP and function)
@theengineersdesk7930
7 ай бұрын
Next one
@Sachin_Jalal
2 ай бұрын
Prosedure????
@shaikhibrahim8302
Ай бұрын
Sir which application/software are you using in this video? because i have been using oracle 11 g command line from last 10 the inreface of oracle command line is very complicated i am facing issue to parcticing SQL and PLSQL questions.can you please provide me a link of application/software that are you using in your video Thank you.
@theengineersdesk7930
Ай бұрын
This is SQL server management studio (ssms)
@BanchhanidhiPradhan66
4 ай бұрын
Whisch sofwate u are using bro?
@theengineersdesk7930
4 ай бұрын
ssms
@gamex-animex6289
Жыл бұрын
Isn't this part of plsql?
@oscaroscuro
5 ай бұрын
difference is who they are mainly used by
@RandomHindu
2 ай бұрын
Where is the difference part ?
@theengineersdesk7930
2 ай бұрын
It was covered in next video
@tok1879
Жыл бұрын
So what's the difference?
@theengineersdesk7930
Жыл бұрын
Watch next shorts please, I have mention the difference
@RaviShankar-xy6zh
Жыл бұрын
Stored procedures are used to perform tranaactions. Stored proecedures can have IN, INOUT and OUT variables. All the database operations insert,update and delete can be performed Exception Handling can be done. Need not return any value. We cannot call procedures from select or having and where clauses. Function: A function only supports IN parameters. No output parameters are supported. Function can be called from select clause. Functions can't call stored procedures. whereas stored procedures can call functions. Functions should always return some value. Functions cannot perform all database operations in it. Only select is allowed. Functions do not provide exception handling. No transactions are allowed.
@AshutoshMishra-tc7cg
3 күн бұрын
Pronounce it as procezur.
@mohanvnaidu
Жыл бұрын
good
@theengineersdesk7930
Жыл бұрын
Thanks
@fired_developer
Жыл бұрын
Where is the answer
@theengineersdesk7930
Жыл бұрын
Next part, this video is about sp and function creation and calling, now when I explain the difference it will be easy to understand
@RaviShankar-xy6zh
Жыл бұрын
Stored procedures are used to perform tranaactions. Stored proecedures can have IN, INOUT and OUT variables. All the database operations insert,update and delete can be performed Exception Handling can be done. Need not return any value. We cannot call procedures from select or having and where clauses. Function: A function only supports IN parameters. No output parameters are supported. Function can be called from select clause. Functions can't call stored procedures. whereas stored procedures can call functions. Functions should always return some value. Functions cannot perform all database operations in it. Only select is allowed. Functions do not provide exception handling. No transactions allowed.
@fired_developer
Жыл бұрын
thank you@@RaviShankar-xy6zh
@shivangisrivastav3336
7 ай бұрын
Kuch smjh hi ni aaya kya smjhaya
@theengineersdesk7930
7 ай бұрын
I m sorry to hear that, any particular part you want to highlight?
Пікірлер: 34