Pregunta Función frente a procedimiento almacenado en SQL Server


He estado aprendiendo las Funciones y el Procedimiento Almacenado por bastante tiempo, pero no sé por qué y cuándo debería usar una función o un procedimiento almacenado. Me parecen iguales, tal vez porque soy un poco novato sobre eso.

¿Puede alguien decirme por qué?


655
2017-07-24 19:40


origen


Respuestas:


Las funciones son valores calculados y no pueden realizar cambios ambientales permanentes en SQL Server (es decir, no se permiten instrucciones INSERT o UPDATE).

Una función se puede usar en línea en las declaraciones de SQL si devuelve un valor escalar, o se puede unir si devuelve un conjunto de resultados.

Un punto que vale la pena destacar de los comentarios, que resumen la respuesta. Gracias a @Sean K Anderson:

Las funciones siguen la definición de computación-competencia porque DEBEN devolver un valor y no pueden alterar los datos que reciben como parámetros   (los argumentos). Las funciones no pueden cambiar nada, debe   tener al menos un parámetro, y deben devolver un valor. Almacenado   procs no tienen que tener un parámetro, pueden cambiar los objetos de la base de datos,   y no tiene que devolver un valor


552
2017-07-24 19:42



La diferencia entre SP y UDF se detalla a continuación:

+---------------------------------+----------------------------------------+
| Stored Procedure (SP)           | Function (UDF - User Defined           |
|                                 | Function)                              |
+---------------------------------+----------------------------------------+
| SP can return zero , single or  | Function must return a single value    |
| multiple values.                | (which may be a scalar or a table).    |
+---------------------------------+----------------------------------------+
| We can use transaction in SP.   | We can't use transaction in UDF.       |
+---------------------------------+----------------------------------------+
| SP can have input/output        | Only input parameter.                  |
| parameter.                      |                                        |
+---------------------------------+----------------------------------------+
| We can call function from SP.   | We can't call SP from function.        |
+---------------------------------+----------------------------------------+
| We can't use SP in SELECT/      | We can use UDF in SELECT/ WHERE/       |
| WHERE/ HAVING statement.        | HAVING statement.                      |
+---------------------------------+----------------------------------------+
| We can use exception handling   | We can't use Try-Catch block in UDF.   |
| using Try-Catch block in SP.    |                                        |
+---------------------------------+----------------------------------------+

464
2017-10-15 17:00



Las funciones y los procedimientos almacenados sirven para propósitos separados. Aunque no es la mejor analogía, las funciones se pueden ver literalmente como cualquier otra función que usarías en cualquier lenguaje de programación, pero los procesos almacenados son más como programas individuales o un script por lotes.

Las funciones normalmente tienen una salida y opcionalmente entradas. La salida se puede usar como entrada para otra función (un servidor SQL incorporado, como DATEDIFF, LEN, etc.) o como un predicado de una consulta SQL, por ejemplo, SELECT a, b, dbo.MyFunction(c) FROM table o SELECT a, b, c FROM table WHERE a = dbo.MyFunc(c).

Los procesos almacenados se utilizan para vincular consultas SQL en una transacción e interactuar con el mundo exterior. Los marcos como ADO.NET, etc. no pueden invocar directamente una función, pero pueden invocar un proceso almacenado directamente.

Sin embargo, las funciones tienen un peligro oculto: pueden ser mal utilizadas y causar problemas de rendimiento desagradables: considere esta consulta:

SELECT * FROM dbo.MyTable WHERE col1 = dbo.MyFunction(col2)

Donde MyFunction se declara como:

CREATE FUNCTION MyFunction (@someValue INTEGER) RETURNS INTEGER
AS
BEGIN
   DECLARE @retval INTEGER

   SELECT localValue 
      FROM dbo.localToNationalMapTable
      WHERE nationalValue = @someValue

   RETURN @retval
END

Lo que sucede aquí es que se llama a la función MyFunction para cada fila en la tabla MyTable. Si MyTable tiene 1000 filas, entonces hay otras 1000 consultas ad-hoc contra la base de datos. De forma similar, si se llama a la función cuando se especifica en la especificación de la columna, se llamará a la función para cada fila devuelta por SELECT.

Entonces necesitas ser cuidadoso escribiendo funciones. Si selecciona SELECCIONAR de una tabla en una función, debe preguntarse si se puede realizar mejor con un JOIN en el proceso primario almacenado o en algún otro constructo SQL (como CASE ... WHEN ... ELSE ... FIN).


180
2017-07-24 20:06



Escriba una función definida por el usuario cuando desee calcular y devolver un valor para usar en otras declaraciones SQL; escriba un procedimiento almacenado cuando lo que desee es agrupar un conjunto posiblemente complejo de instrucciones SQL. ¡Estos son dos casos de uso bastante diferentes, después de todo!


52
2017-07-24 19:42



Diferencias entre los procedimientos almacenados y las funciones definidas por el usuario:

  • Los procedimientos almacenados no se pueden usar en las declaraciones Select.
  • Los procedimientos almacenados admiten la resolución diferida del nombre.
  • Los procedimientos almacenados generalmente se utilizan para llevar a cabo la lógica comercial.
  • Los procedimientos almacenados pueden devolver cualquier tipo de datos.
  • Los procedimientos almacenados pueden aceptar un mayor número de parámetros de entrada que las funciones definidas por el usuario. Los procedimientos almacenados pueden tener hasta 21,000 parámetros de entrada.
  • Los procedimientos almacenados pueden ejecutar SQL dinámico.
  • Los procedimientos almacenados admiten el manejo de errores.
  • Las funciones no deterministas se pueden usar en procedimientos almacenados.

  • Las funciones definidas por el usuario se pueden usar en las declaraciones Select.
  • Las funciones definidas por el usuario no admiten la Resolución de nombre diferido.
  • Las funciones definidas por el usuario generalmente se usan para cálculos.
  • Las funciones definidas por el usuario deberían devolver un valor.
  • Las funciones definidas por el usuario no pueden devolver imágenes.
  • Las funciones definidas por el usuario aceptan números más pequeños de parámetros de entrada que los procedimientos almacenados. Las UDF pueden tener hasta 1.023 parámetros de entrada.
  • Las tablas temporales no se pueden usar en funciones definidas por el usuario.
  • Las funciones definidas por el usuario no pueden ejecutar SQL dinámico.
  • Las funciones definidas por el usuario no admiten la gestión de errores. RAISEERROR O @@ERROR no están permitidos en UDF.
  • Las funciones no deterministas no se pueden usar en UDF. Por ejemplo, GETDATE() no se puede usar en UDF.

51
2018-05-02 09:52



Diferencia básica

La función debe devolver un valor, pero en el Procedimiento almacenado es opcional (El procedimiento puede devolver cero o n valores).

Las funciones solo pueden tener parámetros de entrada mientras que los Procedimientos pueden tener parámetros de entrada / salida.

La función toma un parámetro de entrada, es obligatorio, pero el procedimiento almacenado puede tomar para n parámetros de entrada.

Las funciones se pueden llamar desde el Procedimiento, mientras que los Procedimientos no se pueden llamar desde la Función.

Diferencia de avance

El procedimiento permite la instrucción SELECT y DML (INSERT / UPDATE / DELETE) mientras que Function solo permite la instrucción SELECT.

Los procedimientos no se pueden utilizar en una instrucción SELECT mientras que la Función se puede incrustar en una instrucción SELECT.

Los procedimientos almacenados no se pueden usar en las sentencias de SQL en ninguna parte de la sección WHERE / HAVING / SELECT mientras que Function puede serlo.

Las funciones que devuelven tablas se pueden tratar como otro conjunto de filas. Esto se puede usar en JOINs con otras tablas.

La función en línea se puede considerar como vistas que toman parámetros y se pueden usar en uniones y otras operaciones de conjuntos de filas.

La excepción puede manejarse mediante un bloque try-catch en un procedimiento, mientras que el bloque try-catch no se puede usar en una función.

Podemos ir a Gestión de transacciones en Procedimiento, mientras que no podemos entrar en Función.

fuente


19
2018-02-28 10:55



una función definida por el usuario es una herramienta importante disponible para un programador de servidor sql. Puede usarlo en línea en una declaración SQL como tal

SELECT a, lookupValue(b), c FROM customers 

dónde lookupValue será un UDF. Este tipo de funcionalidad no es posible cuando se usa un procedimiento almacenado. Al mismo tiempo, no puedes hacer ciertas cosas dentro de un UDF. Lo básico para recordar aquí es que UDF:

  • no puede crear cambios permanentes
  • no puede cambiar los datos

un procedimiento almacenado puede hacer esas cosas.

Para mí, el uso en línea de un UDF es el uso más importante de un UDF.


18
2017-07-24 20:04



Procedimientos almacenados  se usan como scripts. Ejecutan una serie de comandos para usted y puede programarlos para que se ejecuten en determinados momentos.

Funciones  se usan como métodos. Usted pasa algo y devuelve un resultado. Debe ser pequeño y rápido, lo hace sobre la marcha.


12
2018-03-13 19:55



Procedimiento almacenado:

  • Es como un programa en miniatura en SQL Server.
  • Puede ser tan simple como una declaración seleccionada, o tan compleja como una larga secuencia de comandos que agrega, elimina, actualiza y / o lee datos de múltiples tablas en una base de datos.
  • (Puede implementar bucles y cursores, que le permiten trabajar con resultados más pequeños o operaciones de fila por fila en los datos).
  • Debería ser llamado usando EXEC o EXECUTE declaración.
  • Devuelve las variables de la tabla, pero no podemos usar OUT parámetro.
  • Admite transacciones.

Función:

  • No se puede usar para actualizar, eliminar o agregar registros a la base de datos.
  • Simplemente devuelve un valor único o un valor de tabla.
  • Solo se puede usar para seleccionar registros. Sin embargo, se puede llamar muy fácilmente desde SQL estándar, como:

    SELECT dbo.functionname('Parameter1')
    

    o

    SELECT Name, dbo.Functionname('Parameter1') FROM sysObjects
    
  • Para operaciones simples de selección reutilizables, las funciones pueden simplificar el código. Solo ten cuidado con el uso JOIN cláusulas en tus funciones. Si tu la función tiene un JOIN cláusula y lo llamas desde otro seleccione declaración que devuelve resultados múltiples, esa función llamará JOIN esas tablas juntas para cada línea devuelta en el conjunto de resultados. Asi que aunque pueden ser útiles para simplificar un poco la lógica, también pueden ser una cuello de botella de rendimiento si no se usan correctamente.

  • Devuelve los valores usando OUT parámetro.
  • No admite transacciones.

6
2018-04-29 11:56



Decidir cuándo usar lo que los siguientes puntos podrían ayudar-

  1. Los procedimientos almacenados no pueden devolver una variable de tabla donde la función puede hacer eso.

  2. Puede usar procedimientos almacenados para alterar los parámetros del entorno del servidor donde, como utiliza funciones que no puede.

aclamaciones


5
2017-07-24 19:54