sql - tabla - ¿Puedo crear una función de uso único en un script o procedimiento almacenado?

procedimiento almacenado para mostrar datos

Question

En SQL Server 2005, ¿existe un concepto de uso único o función local declarada dentro de un script SQL o procedimiento almacenado? Me gustaría abstraer algo de complejidad en un script que estoy escribiendo, pero requeriría poder declarar una función.

Sólo curioso.

Answer 1

En las secuencias de comandos tiene más opciones y una mejor oportunidad de descomposición racional. Mire en el modo SQLCMD (Menú de consulta -> modo SQLCMD), específicamente los comandos: setvar y: r.

Dentro de un procedimiento almacenado, sus opciones son muy limitadas. No puede crear definir una función directamente con el cuerpo de un procedimiento. Lo mejor que puedes hacer es algo como esto, con SQL dinámico:

create proc DoStuff
as begin

  declare @sql nvarchar(max)

  /*
  define function here, within a string
  note the underscore prefix, a good convention for user-defined temporary objects
  */
  set @sql = ''
    create function dbo._object_name_twopart (@object_id int)
    returns nvarchar(517) as
    begin
      return 
        quotename(object_schema_name(@object_id))+N''''.''''+
        quotename(object_name(@object_id))
    end
  ''

  /*
  create the function by executing the string, with a conditional object drop upfront
  */
  if object_id(''dbo._object_name_twopart'') is not null drop function _object_name_twopart
  exec (@sql)

  /*
  use the function in a query
  */
  select object_id, dbo._object_name_twopart(object_id) 
  from sys.objects
  where type = ''U''

  /*
  clean up
  */
  drop function _object_name_twopart

end
go

Esto se aproxima a una función temporal global, si tal cosa existió. Todavía es visible para otros usuarios. Podría agregar @@ SPID de su conexión para unificar el nombre, pero eso requeriría que el resto del procedimiento también use SQL dinámico.

Answer 2

Lo siguiente es lo que he usado en el pasado para lograr la necesidad de una UDF Scalar en MS SQL:

IF OBJECT_ID(''tempdb..##fn_Divide'') IS NOT NULL DROP PROCEDURE ##fn_Divide
GO
CREATE PROCEDURE ##fn_Divide (@Numerator Real, @Denominator Real) AS
BEGIN
    SELECT Division =
        CASE WHEN @Denominator != 0 AND @Denominator is NOT NULL AND  @Numerator != 0 AND @Numerator is NOT NULL THEN
        @Numerator / @Denominator
        ELSE
            0
        END
    RETURN
END
GO

Exec ##fn_Divide 6,4

Este enfoque que utiliza una variable global para el PROCEDIMIENTO le permite hacer uso de la función no solo en sus scripts, sino también en sus necesidades de SQL dinámico.

Answer 3

Puede crear procedimientos almacenados temporales como:

create procedure #mytemp as
begin
   select getdate() into #mytemptable;
end

en una secuencia de comandos SQL, pero no en las funciones. Sin embargo, podrías hacer que el proc almacene su resultado en una tabla temporal, luego usa esa información más adelante en el script.

Answer 4

Sé que podría ser criticado por sugerir SQL dinámico, pero a veces es una buena solución. Solo asegúrese de comprender las implicaciones de seguridad antes de considerar esto.

DECLARE @add_a_b_func nvarchar(4000) = N''SELECT @c = @a + @b;'';
DECLARE @add_a_b_parm nvarchar(500) = N''@a int, @b int, @c int OUTPUT'';

DECLARE @result int;
EXEC sp_executesql @add_a_b_func, @add_a_b_parm, 2, 3, @c = @result OUTPUT;
PRINT CONVERT(varchar, @result); -- prints ''5''

Answer 5

Las expresiones de tabla comunes le permiten definir lo que esencialmente son vistas que duran solo dentro del alcance de sus instrucciones de selección, inserción, actualización y eliminación. Dependiendo de lo que necesite hacer, pueden ser terriblemente útiles.

Answer 6

Puede llamar a la CREATE Function cerca del comienzo de su secuencia de comandos y la DROP Function cerca del final.