none
Ayuda con funcion scalar. El select funciona la funcion no? RRS feed

  • Pregunta

  • Hola muy buenas a todos, me encuentro con un problema, tengo una funcion para recuperar el id de un campo de una tabla y con la funcion me devuelve nul pero si hago la consulta directamente me devuelve el resultado esperado.

    Alguna pista?, gracias, dejo la funcion:

    ALTER FUNCTION [dbo].[IdTipoProveedorDescripcion]
    (
    	@Descripcion nvarchar
    )
    RETURNS int AS
    BEGIN
    	
    
    	DECLARE @Id int;
    
    	SELECT @Id = IdTipoProveedor
    	FROM TiposProveedores
    	WHERE Descripcion =  @Descripcion
    
    	RETURN (@Id)
    
    END


    Al hacer: 

    use ControlDB
    go

    select dbo.IdTipoProveedorDescripcion('PESCADOS') as tipo
    go

    Me devuelve NULL.

    en cambio si hago:

    SELECT IdTipoProveedor
    FROM TiposProveedores
    WHERE Descripcion = 'PESCADOS'

    me devuelve 2  que es el resultado esperado. Agradezco cuanquier pista. Gracias.

    lunes, 31 de agosto de 2015 13:45

Respuestas

  • Adicionalmente para que veas la solución sería hacer algo así.
    ALTER FUNCTION [dbo].[IdTipoProveedorDescripcion]
    (
    	@Descripcion nvarchar(100)
    )
    RETURNS int AS
    BEGIN
    	
    
    	DECLARE @Id int;
    
    	SELECT @Id = IdTipoProveedor
    	FROM TiposProveedores
    	WHERE Descripcion =  @Descripcion
    
    	RETURN (@Id)
    
    END
    De todas formas, estas funciones escalares suelen ser muy mala idea, y penalizar rendimiento, si nos dices como y donde piensas usarlas te podemos dar alguna clave que mejore ese rendimiento.

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    • Marcado como respuesta aratar79 martes, 1 de septiembre de 2015 11:17
    lunes, 31 de agosto de 2015 14:54
    Moderador

Todas las respuestas

  • Es buena practica especificar siempre el largo de las cadenas de carateres cuando se declaran.

    En tu caso el parametro @Descripcion sera de longitud 1 por defecto y por lo tanto el valor 'PESCADOS' sera convertido a 'P' aunque esto no es informado por SQL Server.


    AMB

    Some guidelines for posting questions...

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

    lunes, 31 de agosto de 2015 14:42
  • Adicionalmente para que veas la solución sería hacer algo así.
    ALTER FUNCTION [dbo].[IdTipoProveedorDescripcion]
    (
    	@Descripcion nvarchar(100)
    )
    RETURNS int AS
    BEGIN
    	
    
    	DECLARE @Id int;
    
    	SELECT @Id = IdTipoProveedor
    	FROM TiposProveedores
    	WHERE Descripcion =  @Descripcion
    
    	RETURN (@Id)
    
    END
    De todas formas, estas funciones escalares suelen ser muy mala idea, y penalizar rendimiento, si nos dices como y donde piensas usarlas te podemos dar alguna clave que mejore ese rendimiento.

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    • Marcado como respuesta aratar79 martes, 1 de septiembre de 2015 11:17
    lunes, 31 de agosto de 2015 14:54
    Moderador
  • Primero gracias por las respuestas.

    El realidad pensaba que era bueno usar funciones y procedimientos en vez de hacer las consultas en mi aplicación C#, bueno tengo un poco de lio ya que soy autodidacta y me cuesta una eternidad filtrar toda la información que hay al respecto.

    Estoy trabajando en una base de datos que guarda Proveedores, Ingredientes y recetas ya que trabajo en un restaurante y estoy intentado hacer una aplicación para la gestión de escandallos y recetas.

    Esta función la había pensado porque tengo varios procedientos que me devuelven un listado de ingredientes y los quiero filtrar por Tipo de Proveedor (Carnes, Pescados, Delicatesen, Pasteleria, Etc) o por proveedor y tanto en uno como en otro necesito en numero de Id de cada uno. Dejo uno de los procedimiento aqui:

    CREATE PROCEDURE [dbo].[GetListadosIngredientesTiposProveedor]
    (
    	@Id INT
    
    ) AS
    BEGIN TRY
    
    	SELECT i.IdIngrediente
    		  ,i.Descripcion
    		  ,v1.iva as IvaCompra
    		  ,v2.iva as IvaVenta
    		  ,dbo.PrecioMedio(i.Idingrediente) as Precio
    		  ,dbo.PrecioIva(i.IdIngrediente) as PrecioIva
              ,i.EsVenta
              ,i.Merma
    		  ,p.Nombre as Proveedor
    		  ,d.Descripcion as TiposProveedores
         FROM Ingredientes AS i
         LEFT JOIN TiposIva v1 ON i.IvaCompra = v1.IdIva
         LEFT JOIN TiposIva v2 ON i.IvaVenta = v2.IdIva 
    	 LEFT JOIN Proveedores p ON p.IdProveedor in 
    		(
    			SELECT p1.IdProveedor 
    			FROM IngredientesProveedores as p1
    			WHERE p1.IdIngrediente = i.IdIngrediente
    		)
    	LEFT JOIN TiposProveedores d ON p.TipoProveedor = d.IdTipoProveedor
    	WHERE d.IdTipoProveedor = @Id
    	ORDER BY i.Descripcion
    
    END TRY
    BEGIN CATCH 
    
    	PRINT 'Se ha producido un error';
    	SELECT ERROR_MESSAGE() as ErrorMesssage;
    
    END CATCH

    lunes, 31 de agosto de 2015 15:22
  • Hola aratar79,

    Si tienes consultas complejas puedes encapsularlas y reutilizarlas a través de una vista (mecanismo de abstracción), tus procedimientos podrían hacer uso de una vista como si de una tabla se tratase.

    Por otro lado, observando el procedimiento que nos compartes:

    • ¿Los ingredientes tienen un IVA de compra y venta como relación? Para el caso del IVA las tablas la deberías usar como valor y no referencia, es decir, el IVA debería ser un valor puesto en una columna.
    • [LEFT JOIN Proveedores], combinas con una subconsulta de proveedores basados en la columna ingrediente de sí misma con la tabla Ingredientes. ¿Por qué no combinas las filas basándote en esa columna? no necesitas la subconsulta. (LEFT JOIN Proveedores p ON p.IdIngrediente = i.IdIngrediente)
    • ¿Qué tanta lógica abstraes en PrecioMedio y PrecioIva? Se me ocurre que esos datos podrían estar en una vista y lo único que tendrías que hacer es una combinación basada en la columna IdIngrediente


    lunes, 31 de agosto de 2015 16:54
  • Gracias por tu respuesta, necesito leer aun mas informacion como ya te digo soy autodidacta. Respecto a lo que me dices:

    El iva lo pongo como referencia ya que en España los restaurantes podemos comprar con IVA del 4, 10 o 21 por eso lo hago asi ya que trabajamos con varios ivas. La leche o el pan seria el 4% el alcohol como el vino para cocinar seria el 21%.

    [LEFT JOIN Proveedores], lo hago asi ya que la relacion Proveedor Ingredientes es muchos a muchos un proveedor puede tener muchos ingredientes pero un ingrediente puede ser vendido por muchos proveedores, asi que tengo una tabla Proveedores, otra Ingredientes y otra que me enlaza Ingredientes y Proveedores.

    Respescto a lo de las funciones de precio no conocia otra manera de hacerlo, investigare lo de las vistas, algo que desconozco totalmente. 

    Te doy las gracias, ya que con respuestas como las de hoy uno aprende un monton, repito me cuesta mucho filtrar toda la informacion que encuentro al respecto.

    Un saludo y repito gracias.

    lunes, 31 de agosto de 2015 19:16
  • Hola aratar79,

    Respecto al IVA, lo que refiero es que a la tabla Ingredientes no debería llegar una relación con la tabla TiposIva, sino sólo el valor: 4, 10 o 21, es más, ni siquiera se graba el porcentaje, sino el monto que significa el porcentaje sobre el valor: Valor de venta 100, IVA 4, Total 104.

    Respecto a las combinaciones, si tienes una tabla que junta la combinación de Ingredientes y Proveedores entonces el Join debería ser entre Ingredientes e IngredientesProveedores, sigo sin ver la necesidad de la subconsulta.

    FROM 
    	Ingredientes AS i
        LEFT JOIN TiposIva AS v1 ON i.IvaCompra = v1.IdIva
        LEFT JOIN TiposIva AS v2 ON i.IvaVenta = v2.IdIva 
    	LEFT JOIN IngredientesProveedores AS ip ON (i.IdIngrediente = ip.IdIngrediente)
    	LEFT JOIN Proveedores p ON (ip.IdProveedor = p.IdProveedor)
    	LEFT JOIN TiposProveedores d ON p.TipoProveedor = d.IdTipoProveedor

    La idea de tener la vista es que puedas tener un conjunto de resultados con las columnas IdIngrediente, PrecioMedio, PrecioIva y la combines como una tabla mas.

    SELECT
            ...
            mv.PrecioMedio,
            mv.PrecioIVA,
            ...
    
    FROM 
    	Ingredientes AS i
        LEFT JOIN TiposIva AS v1 ON i.IvaCompra = v1.IdIva
        LEFT JOIN TiposIva AS v2 ON i.IvaVenta = v2.IdIva 
    	LEFT JOIN IngredientesProveedores AS ip ON (i.IdIngrediente = ip.IdIngrediente)
    	LEFT JOIN Proveedores p ON (ip.IdProveedor = p.IdProveedor)
    	LEFT JOIN TiposProveedores d ON p.TipoProveedor = d.IdTipoProveedor
            LEFT JOIN MiVista AS mv  ON (i.IdIngrediente = mv.IdIngrediente)


    OFF TOPIC:

    Máximas en el diseño de software:

    Principio 1: DRY (Don't repeat yourself) No repitas código, encapsula y reutiliza.

    Principio 2: KISS (Keep It Simple, Stupid) Hazlo simple, legible, evita la complejidad innecesaria.

    Principio 3: YAGNI (You Aint't Gonna Need It) No agregues funcionalidad si no es necesario. Básate en requerimientos, no en suposiciones.
    lunes, 31 de agosto de 2015 21:43
  • estas casi en lo correcto, y tu query con los índices adecuados no tiene por que ir mal. Cuando hablo de que las funciones escalares dan mal rendimiento es porque ejecutan el query que encapsulan por cada fila,.. cuantas mas filas.. mas tiempo mientras que si le dejas a SQL Server optimizar el query irá mejor. la solución para eso es sencilla, puedes crear una función inline, e invocarla con cross apply, sería algo así en tu caso

    create FUNCTION [dbo].[IdTipoProveedorDescripcion]
    (
    	@Descripcion nvarchar(100)
    )
    RETURNS table AS return (
    	
    
    	SELECT IdTipoProveedor
    	FROM TiposProveedores
    	WHERE Descripcion =  @Descripcion
    
    	)


    tu select para precio medio quedaría a´si, tu tendrás que cambiar precio iva... pero es la misma idea

    SELECT i.IdIngrediente
    		  ,i.Descripcion
    		  ,v1.iva as IvaCompra
    		  ,v2.iva as IvaVenta
    		  ,p.preciomedio as Precio
    		  ,dbo.PrecioIva(i.IdIngrediente) as PrecioIva
              ,i.EsVenta
              ,i.Merma
    		  ,p.Nombre as Proveedor
    		  ,d.Descripcion as TiposProveedores
         FROM Ingredientes AS i
         LEFT JOIN TiposIva v1 ON i.IvaCompra = v1.IdIva
         LEFT JOIN TiposIva v2 ON i.IvaVenta = v2.IdIva 
    	 LEFT JOIN Proveedores p ON p.IdProveedor in 
    		(
    			SELECT p1.IdProveedor 
    			FROM IngredientesProveedores as p1
    			WHERE p1.IdIngrediente = i.IdIngrediente
    		)
    	LEFT JOIN TiposProveedores d ON p.TipoProveedor = d.IdTipoProveedor
    	cross apply tufuncionpreciomedio(i.idingrediente) p
    	WHERE d.IdTipoProveedor = @Id
    	ORDER BY i.Descripcion

    para que entre todos no te liemos, te diré que las vistas y las funciones inline como las que te he escrito son realmente muy muy parecidas, podríamos decir sin ser muy puristas que una función inline no es ni mas ni menos que una vista que admite parámetros. De ahí que mi recomendación no vaya a vistas, sino a este tipo de cosas. Por otro lado, en mi experiencia las vistas que no son malas de por si... acaban oscurenciendo todo, nos acostumbramos y acabamos haciendo vistas de vistas de vistas de vistas... que resultan tremendamente malas para el rendimiento. Insisto, no es que las vistas tengan ningún problema, no lo tienen en absoluto, pero en mi  experiencia, he visto muchos casos en que se han convertido en un infierno porque se ha malentendido lo que significa reusar...


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA


    lunes, 31 de agosto de 2015 22:19
    Moderador