Creating a user defined function in SQL:

First of all, I have this SQL petition:

SELECT * FROM Articulos AS a WHERE filtro = 6 
AND EXISTS
(SELECT * FROM Embalajes AS b
WHERE a.IdArticulo = b.IdArticulo)

..What it does is return every entry from the column 'IdArticulo' in the table 'Articulo' which also exists in the table 'Embalajes'. If the IdArticulo in 'Articulo' doesn't exist in 'Embalajes', it will not be returned.

...Now I want to create a user defined function in SQL which will take an IdArticulo as a parameter, formatted as a string. The function will then search and see if the said IdArticulo exists both in the table 'Articulo' and the table Embalaje'. If It exists in both, the function will return a value of 1. Otherwise, it will return a value of 0.

I am not quite sure how to format this function. At the moment this is what I have:

CREATE FUNCTION dbo.EsModulacion('E134565432)
RETURNS int
BEGIN
--Insert functionality here
END;

Any help would be appreciated. Thank you.

hupp

2 years ago

Level 11

@lord_gordon i try to make it for you. Please check

CREATE FUNCTION dbo.EsModulacion (@IdArticulo VARCHAR(50))
RETURNS INT
AS
BEGIN
    DECLARE @ExistsInArticulo INT
    DECLARE @ExistsInEmbalajes INT

    -- Check if the IdArticulo exists in 'Articulo' table
    SELECT @ExistsInArticulo = CASE WHEN EXISTS (SELECT 1 FROM Articulos WHERE IdArticulo = @IdArticulo) THEN 1 ELSE 0 END

    -- Check if the IdArticulo exists in 'Embalajes' table
    SELECT @ExistsInEmbalajes = CASE WHEN EXISTS (SELECT 1 FROM Embalajes WHERE IdArticulo = @IdArticulo) THEN 1 ELSE 0 END

    -- Return 1 if the IdArticulo exists in both tables, otherwise return 0
    RETURN CASE WHEN @ExistsInArticulo = 1 AND @ExistsInEmbalajes = 1 THEN 1 ELSE 0 END
END;

To call above created function

DECLARE @IdArticuloToCheck VARCHAR(50)
SET @IdArticuloToCheck = 'E134565432'

SELECT dbo.EsModulacion(@IdArticuloToCheck) AS Result;

Let me know your feedback.

Please or to participate in this conversation.