none
Declaración de variables en una función que devuelve tabla RRS feed

  • Pregunta

  • Buenos días.

    Quisiera saber si es posible declarar una variable con un valor constante que se utilizará dentro de una función que devuelve table, de manera que si el dato cambia sólo lo cambie en la variable y no en toda la consulta.

    La función es la siguiente (Es mucho más larga, sólo pongo una parte)

    El problema es que coloque donde coloque la declaración de la variable y la asignación del valor, me da error.

    Muchas gracias de antemano


    ALTER FUNCTION [dbo].[Función] (@COD NVARCHAR(5))

    RETURNS TABLE return

    DECLARE @TEPKW INT
    SET @TEPKW = 15290


    SELECT 1 AS Id, 'Volumen ' AS Texto, (SELECT Vol FROM Vols WHERE COD = @COS AS Dato

    UNION

    SELECT 2 AS Id, 'Energía' AS Texto, CONVERT(Decimal(9,2), ROUND((SELECT Vol FROM Vols WHERE COD = @COD * @TEPKW / 100, 2)) AS Dato

    UNION

    SELECT 3 AS Id, 'Litros' AS Texto,  Litros * @TEPKW / 100 AS Dato FROM CarCmb WHERE COD = @COD

    • Editado FPLS martes, 18 de abril de 2017 12:08
    martes, 18 de abril de 2017 11:59

Respuestas

  • Muchas gracias.

    Ya lo solucioné utilizando tus indicaciones.

    Muchas gracias

    ALTER FUNCTION [dbo].[Función] (@COD NVARCHAR(5))

    RETURNS TABLE return

    WITH TEPKW AS (SELECT 15290 AS Valor)


    SELECT 1 AS Id, 'Volumen ' AS Texto, (SELECT Vol FROM Vols WHERE COD = @COS AS Dato

    UNION

    SELECT 2 AS Id, 'Energía' AS Texto, CONVERT(Decimal(9,2), ROUND((SELECT Vol FROM Vols WHERE COD = @COD * (SELECT Valor FROM TEPKW) / 100, 2)) AS Dato

    UNION

    SELECT 3 AS Id, 'Litros' AS Texto,  Litros * (SELECT Valor FROM TEPKW) / 100 AS Dato FROM CarCmb WHERE COD = @COD

    martes, 18 de abril de 2017 16:12

Todas las respuestas

  • FPLS,

    En este tipo de funciones no es permitido la declaracion de variables.

    En cuanto a lo que deseas hacer, pudieras usar uno de estos metodos:

    - Un parametro con valor por defecto y siempre que llames a la funcion usar DEFAULT en ese parametro

    - Usar una CTE y luego usar el operador APPLY

    Ejemplos:

    CREATE FUNCTION dbo.ufn_F1 (
    @p1 int,
    @p2 int = 15290
    )
    RETURNS TABLE
    AS
    RETURN (
    SELECT @p1 + @p2 AS col1
    );
    GO
    SELECT col1 FROM dbo.ufn_F1(1000, DEFAULT) AS T;
    GO
    CREATE FUNCTION dbo.ufn_F2 ()
    RETURNS TABLE
    AS
    RETURN (
    WITH R AS (
    SELECT 15290 AS col1
    )
    SELECT S.col1
    FROM (VALUES (1000), (2000), (3000)) AS T(col1) CROSS APPLY (SELECT T.col1 + R.col1 AS col1 FROM R) AS S
    );
    GO
    SELECT col1 FROM dbo.ufn_F2() AS T;
    GO
    DROP FUNCTION dbo.ufn_F1, dbo.ufn_F2;
    GO


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 18 de abril de 2017 13:12
  • Hola Hunchback.

    Muchas gracias por tu respuesta, pero no ha funcionado.

    He intentado hacer cambios utilizando sólo la parte de WITH,pero tampoco me da resultado.

    He entendido lo que quieres hacer,pero como nunca he utilizado el WITH ni CROSS APPLY, no se cómo hacerle modificaciones. Podrías hacer el ejemplo un poco más sencillo, por ejemplo, que simplemente muestre el valor de R.col

    He utilizado la parte de segunda función y me salen los siguientes errores:

    Función:

    CREATE FUNCTION dbo.ufn_F2 ()
    RETURNS TABLE
    AS
    RETURN (
    WITH R AS (
    SELECT 15290 AS col1
    )
    SELECT S.col1
    FROM (VALUES (1000), (2000), (3000)) AS T(col1) CROSS APPLY (SELECT T.col1 + R.col1 AS col1 FROM R) AS S
    );
    GO
    SELECT col1 FROM dbo.ufn_F2() AS T;
    GO

    Los errores son los siguientes

    Mens 156, Nivel 15, Estado 1, Procedimiento ufn_F2, Línea 9
    Sintaxis incorrecta cerca de la palabra clave 'VALUES'.
    Mens 156, Nivel 15, Estado 1, Procedimiento ufn_F2, Línea 9
    Sintaxis incorrecta cerca de la palabra clave 'AS'.
    Mens. 208, Nivel 16, Estado 1, Línea 1
    El nombre de objeto 'dbo.ufn_F2' no es válido.

    martes, 18 de abril de 2017 15:48
  • Muchas gracias.

    Ya lo solucioné utilizando tus indicaciones.

    Muchas gracias

    ALTER FUNCTION [dbo].[Función] (@COD NVARCHAR(5))

    RETURNS TABLE return

    WITH TEPKW AS (SELECT 15290 AS Valor)


    SELECT 1 AS Id, 'Volumen ' AS Texto, (SELECT Vol FROM Vols WHERE COD = @COS AS Dato

    UNION

    SELECT 2 AS Id, 'Energía' AS Texto, CONVERT(Decimal(9,2), ROUND((SELECT Vol FROM Vols WHERE COD = @COD * (SELECT Valor FROM TEPKW) / 100, 2)) AS Dato

    UNION

    SELECT 3 AS Id, 'Litros' AS Texto,  Litros * (SELECT Valor FROM TEPKW) / 100 AS Dato FROM CarCmb WHERE COD = @COD

    martes, 18 de abril de 2017 16:12
  • Me parece muy bien que lo hayas intentado por ti mismo.

    Quizas esto fue lo que sugeri:

    ALTER FUNCTION [dbo].[Función] (
    @COD NVARCHAR(5)
    )
    RETURNS TABLE 
    AS
    RETURN (
    WITH TEPKW AS (
    SELECT 15290 AS Valor
    )
    SELECT 
    	1 AS Id, 'Volumen ' AS Texto, Vol AS Dato
    FROM
    	Vols 
    WHERE
    	COD = @COD
    
    UNION ALL
    
    SELECT
    	2 AS Id, 'Energía' AS Texto, CONVERT(Decimal(9,2), ROUND(V.Vol * P.Valor / 100, 2)) AS Dato
    FROM 
    	Vols AS V
    	CROSS APPLY
    	(SELECT Valor FROM TEPKW) AS P
    WHERE 
    	COD = @COD
    
    UNION ALL
    
    SELECT
    	3 AS Id, 'Litros' AS Texto,  C.Litros * P.Valor / 100 AS Dato 
    FROM
    	CarCmb AS C
    	CROSS APPLY
    	(SELECT Valor FROM TEPKW) AS P
    WHERE
    	COD = @COD
    );
    GO

    No sabemos el tipo de dato de las columnas [Vol] y [Litros] pero de ser enteros entonces pudieras dividir por el valor 100.00 para evitar la division de enteros o promover el valor a un tipo de dato decimal.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    martes, 18 de abril de 2017 16:49