sql - funciones - sintaxis de procedimientos almacenados

Funciones vs Procedimientos almacenados

Question

Digamos que tengo que implementar una parte del código T-SQL que debe devolver una tabla como resultado. Puedo implementar una función con valores de tabla o un procedimiento almacenado que devuelve un conjunto de filas. ¿Qué debería usar?

En resumen, lo que quiero saber es:

¿Cuáles son las principales diferencias entre funciones y procedimientos almacenados? ¿Qué consideraciones debo tener en cuenta para usar una u otra?

Answer 1

1. El procedimiento puede devolver cero o n valores mientras que la función puede devolver un valor que es obligatorio.

2. Los procedimientos pueden tener parámetros de entrada / salida, mientras que las funciones solo pueden tener parámetros de entrada.

3. El procedimiento permite seleccionar tanto como la declaración DML en él, mientras que la función solo permite seleccionar una declaración en ella.

4. Las funciones pueden llamarse desde el procedimiento, mientras que los procedimientos no pueden invocarse desde la función.

5. 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.

6. Podemos ir a la gestión de transacciones en el procedimiento, mientras que no podemos entrar en funcionamiento.

7. Los procedimientos no se pueden utilizar en una declaración de selección, mientras que la función se puede insertar en una declaración de selección.

8. UDF (función definida por el usuario) se puede utilizar en las sentencias SQL en cualquier lugar de la sección WHERE / HAVING / SELECT , mientras que los procedimientos almacenados no pueden ser.

9. Las UDF que devuelven tablas se pueden tratar como otro conjunto de filas. Esto se puede usar en JOIN s con otras tablas.

10. Las UDF en línea se pueden considerar como vistas que toman parámetros y se pueden usar en JOIN s y otras operaciones del conjunto de filas.

Answer 2

Como se mencionó anteriormente, las funciones son más legibles / compostables / auto documentadas, pero son menos efectivas en general, y pueden ser mucho menos efectivas si te dejas llevar por ellas en combinaciones como

SELECT *
FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1
INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2
    ON (tvf1.JoinId = tvf2.JoinId)

A menudo, solo tiene que aceptar la redundancia de código que un TVF podría eliminar (a un costo de rendimiento inaceptable).

Otro punto que aún no he mencionado es que no se pueden usar tablas temporales de cambio de estado de la base de datos dentro de un multi-statement tvf. El mecanismo más funcionalmente equivalente para una tabla temporal es el cambio no estatal, en la variable de tabla de memoria, y para conjuntos de datos grandes, una tabla temporal probablemente será más eficiente que una variable de tabla. (Otras alternativas incluyen tablas dinámicas y expresiones de tabla común, pero con cierto nivel de complejidad, dejan de ser una buena opción de la OMI).

Answer 3

Depende :) Si desea usar el resultado con valores de tabla en otro procedimiento, es mejor que use una función TableValued. Si los resultados son para un cliente, el proceso almacenado es generalmente la mejor manera de hacerlo.

Answer 4

Ejecuté algunas pruebas con un poco de lógica de ejecución larga, con el mismo bit de código (una instrucción SELECT larga) ejecutándose en una función de valores de tabla y un procedimiento almacenado, y un EXEC / SELECT directo, y cada uno se realizó de forma idéntica.

En mi opinión, siempre use una función de valores de tabla en lugar de un procedimiento almacenado para devolver un conjunto de resultados, ya que hace que la lógica sea mucho más fácil y legible en las consultas que posteriormente se unen a ellos, y le permite reutilizar la misma lógica. Para evitar un golpe de rendimiento excesivo, a menudo uso parámetros "opcionales" (es decir, puede pasarles NULL) para permitir que la función devuelva el conjunto de resultados para que sea más rápido, por ejemplo:

CREATE FUNCTION dbo.getSitePermissions(@RegionID int, @optPersonID int, optSiteID int)
AS
RETURN 
    SELECT DISTINCT SiteID, PersonID
    FROM dbo.SiteViewPermissions
    WHERE (@optPersonID IS NULL OR @optPersonID = PersonID)
    AND (@optSiteID IS NULL OR @optSiteID = SiteID)
    AND @RegionID = RegionID

De esta forma puede usar esta función para muchas situaciones diferentes, y no tome un gran golpe de rendimiento. Creo que esto es más eficiente que el filtrado posterior:

SELECT * FROM dbo.getSitePermissions(@RegionID) WHERE SiteID = 1

He utilizado esta técnica en varias funciones, a veces con una larga lista de parámetros "opcionales" de este tipo.

Answer 5

Las funciones deben ser deterministas y no pueden usarse para realizar cambios en la base de datos, mientras que los procedimientos almacenados le permiten hacer inserciones y actualizaciones, etc.

Debe limitar su uso de funciones, ya que representan un gran problema de escalabilidad para consultas grandes y complejas. Se convierten en una especie de "caja negra" para el optimizador de consultas, y verá enormes diferencias de rendimiento entre el uso de funciones y simplemente insertando el código en una consulta.

Pero definitivamente son útiles para retornos con valores de tabla en casos muy específicos.

Si necesita analizar una lista delimitada por comas, para simular el paso de una matriz a un procedimiento, una función puede convertir la lista en una tabla para usted. Esta es una práctica común con Sql Server 2005, ya que aún no podemos pasar las tablas a los procedimientos almacenados (podemos hacerlo en 2008).

Answer 6

Me gustaría probar ambos. Es probable que el enfoque sp o una tabla derivada sea significativamente más rápido que una función y, si es así, debería utilizarse ese enfoque. En general, evito las funciones porque pueden ser cerdas de rendimiento.

Answer 7

Si es probable que desee combinar el resultado de este fragmento de código con otras tablas, obviamente una función con valores de tabla le permitirá componer los resultados en una sola instrucción SELECT.

Generalmente, hay una jerarquía (Ver <Función de TV <Proc Almacenado). Puede hacer más en cada uno, pero la capacidad de componer los resultados y que el optimizador se involucre realmente disminuye a medida que aumenta la funcionalidad.

Así que use el que mínimamente le permita expresar el resultado deseado.

Answer 8

Si tiene una función, podría usarla como parte de su declaración SQL, por ejemplo

SELECT function_name(field1) FROM table

No funciona de esta manera para los procedimientos almacenados.

Answer 9

Voy a escribir algunas diferencias interesantes entre los procedimientos almacenados y las funciones.

• Podemos usar funciones en consultas de selección, pero no podemos usar procedimientos almacenados en consultas seleccionadas.

• No podemos usar funciones no deterministas en Funciones pero podemos usar funciones no deterministas en procedimientos almacenados. Ahora surge la pregunta, ¿cuál es la función no determinista ... Ans es: -

  Una función no determinista es aquella función que devuelve diferentes salidas para los mismos valores de entrada en diferentes momentos, como getdate (). Siempre devuelve un valor diferente cada vez que se ejecuta.

  Excepción:-

  Las versiones anteriores del servidor sql anteriores a sql 2000 no permiten usar la función getdate () en funciones definidas por el usuario, pero la versión 2005 y posteriores nos permite usar la función getdate () dentro de una función definida por el usuario.

  Newid () es otro ejemplo de función no determinista, pero no se puede usar en funciones definidas por el usuario, pero podemos usarlo en procedimientos almacenados.

• Podemos usar sentencias DML (insertar, actualizar, eliminar) dentro de un procedimiento almacenado, pero no podemos usar declaraciones DML en funciones en tablas físicas o tablas permanentes. Si queremos hacer operaciones DML en funciones, podemos hacerlo sobre variables de tablas que no están en tablas permanentes.

• No podemos usar el manejo de errores dentro de la función, pero podemos manejar errores en procedimientos almacenados.

Answer 10

Yo personalmente uso las funciones con valores de tabla cuando todo lo que devuelvo es una sola tabla sin efectos. Básicamente los trato como vistas parametrizadas.

Si necesito que se devuelvan varios conjuntos de registros o si hay valores actualizados en tablas, utilizo un procedimiento almacenado.

Mis 2 centavos

Answer 11

De los documentos :

Si un procedimiento almacenado cumple los siguientes criterios, es un buen candidato para ser reescrito como una función con valores de tabla:

• La lógica se puede expresar en una sola instrucción SELECT, pero es un procedimiento almacenado, en lugar de una vista, solo por la necesidad de parámetros.

• El procedimiento almacenado no realiza operaciones de actualización, excepto para las variables de tabla.

• No hay necesidad de declaraciones EXECUTE dinámicas.

• El procedimiento almacenado devuelve un conjunto de resultados.

• El objetivo principal del procedimiento almacenado es generar resultados intermedios que se deben cargar en una tabla temporal, que luego se consulta en una instrucción SELECT.