valores - ver codigo de stored procedure sql server




Función vs. Procedimiento Almacenado en SQL Server (12)

He estado aprendiendo funciones y procedimientos almacenados durante bastante tiempo, pero no sé por qué y cuándo debo usar una función o un procedimiento almacenado. Me parecen iguales, tal vez porque soy un poco novato en eso.

¿Puede alguien decirme por qué?


¡Las funciones de SQL Server, como los cursores, deben usarse como su última arma! Tienen problemas de rendimiento y, por lo tanto, el uso de una función con valores de tabla debe evitarse tanto como sea posible. Hablar de rendimiento es hablar de una tabla con más de 1,000,000 de registros alojados en un servidor en un hardware de clase media; de lo contrario, no necesita preocuparse por el impacto en el rendimiento causado por las funciones.

  1. Nunca use una función para devolver un conjunto de resultados a un código externo (como ADO.Net)
  2. Use vistas / combinación de procs almacenados tanto como sea posible. puede recuperarse de futuros problemas de crecimiento de rendimiento con las sugerencias que le proporcionará DTA (Database Tuning Adviser) (como vistas y estadísticas indizadas) - ¡a veces!

Para mayor referencia, consulte: http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html


Aquí hay una razón práctica para preferir funciones sobre procedimientos almacenados. Si tiene un procedimiento almacenado que necesita los resultados de otro procedimiento almacenado, debe utilizar una instrucción insert-exec. Esto significa que tiene que crear una tabla temporal y usar una declaración exec para insertar los resultados del procedimiento almacenado en la tabla temporal. Está desordenado. Un problema con esto es que insert-execs no puede ser anidado .

Si está atascado con procedimientos almacenados que llaman a otros procedimientos almacenados, puede encontrarse con esto. Si el procedimiento almacenado anidado simplemente devuelve un conjunto de datos, puede reemplazarse con una función con valores de tabla y ya no aparecerá este error.

( esta es otra razón por la que debemos mantener la lógica empresarial fuera de la base de datos )


Diferencias entre procedimientos almacenados y funciones definidas por el usuario:

  • Los procedimientos almacenados no se pueden usar en declaraciones Select.
  • Los procedimientos almacenados admiten la resolución de nombres diferidos.
  • Los procedimientos almacenados se utilizan generalmente para realizar la lógica empresarial.
  • 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 soportan el manejo de errores.
  • Las funciones no deterministas se pueden utilizar 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 nombres diferidos.
  • Las funciones definidas por el usuario se utilizan generalmente para los cálculos.
  • Las funciones definidas por el usuario deben 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 utilizar 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 el manejo de errores. RAISEERROR O @@ERROR no están permitidos en UDF.
  • Las funciones no deterministas no se pueden utilizar en UDF. Por ejemplo, GETDATE() no se puede utilizar en UDF.

En SQL Server, las funciones y el procedimiento almacenado son dos tipos diferentes de entidades.

Función: En la base de datos de SQL Server, las funciones se utilizan para realizar algunas acciones y la acción devuelve un resultado inmediatamente. Las funciones son dos tipos:

  1. Sistema definido

  2. Usuario definido

Procedimientos almacenados: en SQL Server, los procedimientos almacenados se almacenan en el servidor y pueden devolverse cero, valores únicos y múltiples. Los procedimientos almacenados son dos tipos:

  1. Procedimientos almacenados en el sistema
  2. Procedimientos definidos por el usuario

La diferencia entre SP y UDF se enumera 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.    |                                        |
+---------------------------------+----------------------------------------+

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

Una función se puede usar en línea en sentencias 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 dominio del equipo en el sentido de que DEBEN devolver un valor y no pueden alterar los datos que reciben como parámetros (los argumentos). Las funciones no pueden cambiar nada, deben tener al menos un parámetro y deben devolver un valor. Los procesos almacenados no tienen que tener un parámetro, pueden cambiar los objetos de la base de datos y no tienen que devolver un valor.


Para decidir cuándo usar lo que podrían ayudar los siguientes puntos:

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

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

aclamaciones


Procedimiento almacenado:

  • Es como un programa en miniatura en SQL Server.
  • Puede ser tan simple como una declaración de selección, o tan complejo como un script largo que agrega, elimina, actualiza y / o lee datos de varias tablas en una base de datos.
  • (Puede implementar bucles y cursores, que le permiten trabajar con resultados más pequeños o operaciones fila por fila en los datos).
  • Debe llamarse utilizando la sentencia EXEC o EXECUTE .
  • Devuelve las variables de la tabla, pero no podemos usar el parámetro OUT .
  • Soporta transacciones.

Función:

  • No se puede utilizar para actualizar, eliminar o agregar registros a la base de datos.
  • Simplemente devuelve un solo valor o un valor de tabla.
  • Solo se puede utilizar 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 de selección reutilizables simples, las funciones pueden simplificar el código. Solo desconfíe de usar cláusulas JOIN en sus funciones. Si su función tiene una cláusula JOIN y la llama desde otra instrucción de selección que devuelve múltiples resultados, esa llamada a la función JOIN esas tablas para cada línea devuelta en el conjunto de resultados. Entonces, aunque pueden ser útiles para simplificar algo de lógica, también pueden ser un cuello de botella en el rendimiento si no se usan correctamente.

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

Diferencia basica

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 de uno a 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 anticipada

El procedimiento permite la instrucción SELECT y DML (INSERT / UPDATE / DELETE), mientras que la función 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 ser.

Las funciones que devuelven tablas se pueden tratar como otro conjunto de filas. Esto puede ser usado en unir 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 conjunto de filas.

El bloque try-catch en un procedimiento puede manejar las excepciones, mientras que el bloque try-catch no puede usarse en una función.

Podemos optar por la gestión de transacciones en el procedimiento, mientras que no podemos ir en función.

source


Los procedimientos almacenados se utilizan como scripts . Ejecutan una serie de comandos para usted y puede programar su ejecución en ciertos momentos.

Las funciones se utilizan como métodos. Le pasas algo y te devuelve un resultado. Debe ser pequeño y rápido, lo hace sobre la marcha.


  • Es obligatorio que la función devuelva un valor mientras no sea para el procedimiento almacenado.
  • Las declaraciones seleccionadas solo se aceptan en UDF, mientras que las declaraciones en DML no son necesarias.
  • El procedimiento almacenado acepta cualquier declaración, así como las declaraciones de DML.
  • UDF solo permite entradas y no salidas.
  • El procedimiento almacenado permite tanto entradas como salidas.
  • Los bloques de captura no se pueden usar en UDF, pero se pueden usar en un procedimiento almacenado.
  • No se permiten transacciones en funciones en UDF, pero en el procedimiento almacenado están permitidas.
  • Solo las variables de tabla pueden usarse en UDF y no en tablas temporales.
  • El procedimiento almacenado permite tanto variables de tabla como tablas temporales.
  • UDF no permite que los procedimientos almacenados sean llamados desde funciones, mientras que los procedimientos almacenados permiten la llamada de funciones.
  • UDF se usa en la cláusula de unión, mientras que los procedimientos almacenados no se pueden usar en la cláusula de unión.
  • El procedimiento almacenado siempre permitirá el retorno a cero. UDF, por el contrario, tiene valores que deben regresar a un punto predeterminado.

  • Las funciones se pueden usar en una declaración de selección donde los procedimientos no pueden.

  • El procedimiento almacenado toma los parámetros de entrada y salida, pero las Funciones solo toman parámetros de entrada.

  • Las funciones no pueden devolver valores de texto de tipo, ntext, imagen y marcas de tiempo donde pueden hacerlo los procedimientos.

  • Las funciones se pueden usar como tipos de datos definidos por el usuario en la tabla de creación, pero los procedimientos no pueden.

*** Ej .: -crear table <tablename>(name varchar(10),salary getsal(name))

Aquí getsal es una función definida por el usuario que devuelve un tipo de salario, cuando se crea una tabla, no se asigna almacenamiento para el tipo de salario, y la función getsal tampoco se ejecuta, pero cuando estamos obteniendo algunos valores de esta tabla, la función getsal se ejecuta y el Tipo devuelto se devuelve como el conjunto de resultados.





sql-function