Ayuda con funcion scalar. El select funciona la funcion no?

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

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

Answer 5

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.

Answer 6

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.

Answer 7

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