SQL Server和存储过程之间的区别.NET项目的.vb或.cs文件中的函数或子函数 [英] Difference between stored procedure in SQL Server & function or sub in .vb or .cs file of .NET project

问题描述

存储过程和函数之间的差异或.vb或.cs文件中创建的子元件

基本上存储过程用于执行重复任务，如插入，更新查询等。

您也可以通过在.vb或.cs文件中创建子或函数来执行此操作，然后它们之间有什么区别？

difference between stored procedure and function or sub created in .vb or .cs file
basically stored procedure is used to perform the repeated task like insert,update query etc.
You can also perform this by creating sub or function in .vb or .cs file then what is difference between them?

推荐答案

存储过程与函数：

•我们可以在程序中进行事务管理，但我们不能执行功能。

•过程可以返回零或n值而功能可以返回一个强制值。

•程序可以有输入/输出参数，而函数只能有输入参数。

•内联UDF'可以是虽然是带有参数的视图，但可以在JOIN和其他Rowsetoperations中使用。

•过程允许select和DML语句，而function只允许select语句。
•可以从过程调用函数，但不能从函数调用过程。

•异常可以通过程序中的try-catch块处理，而try-catch块不能在函数中使用。

•在select语句中不能使用过程，而函数可以嵌入在select语句中。

•UDF可以在SQL语句中使用WHERE / HAVING / SELECT部分​​中的任何位置，而不能存储过程。

•返回表的UDF可以视为另一个行集。这可以在与其他表的JOIN中使用。

•还有更多这里 [ ^ ]。





vb中函数和sub之间存在很多差异。请参考以下链接：

VB.net中的子程序和函数 [ ^ ]


VB.Net Sub vs. Function [ ^ ]

功能和子系列介绍 [ ^ ]

还有更多此处 [ ^ ]。



--Amit

Stored Procedures vs Functions:
• We can go for transaction management in procedure whereas we can''t go in function.
• Procedure can return zero or n values whereas function can return one value which is mandatory.
• Procedures can have input/output parameters for it whereas functions can have only input parameters.
• Inline UDF''s can be though of as views that take parameters and can be used in JOINs and other Rowsetoperations.
• Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
• Functions can be called from procedure whereas procedures cannot be called from function.
• Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
• Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.
• UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
• UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
• And a lot more here[^].


And a lots of differences are there between function and sub in vb. Refer the links below:
Sub Procedures and Functions in VB.net[^]

VB.Net Sub vs. Function[^]
An Introduction to Functions and Subs[^]
And a lot more here[^].

--Amit

存储过程和函数构建在基于SQL语言的基础上，并以强制方式对数据进行操作，这意味着您可以说出您想要的内容。不是你想要它和数据库引擎的数据。



SQL是一种强大的数据操作语言，但我是ag ainst在数据库级别编写它，因为您将被锁定在供应商引擎中，并且在需要时无法轻松移动到另一个引擎。如果您在自己的代码中编写SQL语句（可以进行版本检查等），我相信您可以获得所有好处。 [数据库供应商会告诉你，如果你写SP'，它会更高效，但根据我的经验，这不是现代数据库中的情况）

Stored procedures and functions are built on the SQL language which is set based and operates on data in an imperative way meaning you say what you want and not how you want it and the database engine figures that out.

SQL is a powerful language for data manipulations, however I am against writing it at the database level since you will get locked down in a vendors engine and cannot easily move to another when needed. I believe you can get all the benefits if you write the SQL statements in your own code (which you can version check etc.). [DB vendors will sell you that it is more performant if you write SP''s but in my experience this is not the case in modern DB''s]

sub in VB = Method执行操作集并且不返回任何内容



SQL存储过程与函数的对比



进阶功能可用于内联查询



dis adv of function

功能有限错误处理比sp

SP可以使用临时表但功能不能

功能无法调用sp



快乐编码！

：）

sub in VB = Method that performs set of operation and does not return anything

SQL Store procedure vs Function

adv. of function is can use in inline query

dis adv of function
function have limited err handling than sp
SP can use temp table but function can not
function can not call sp

Happy Coding!
:)

这篇关于SQL Server和存储过程之间的区别.NET项目的.vb或.cs文件中的函数或子函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！