0

Can anybody tell why the value of @URlID is always -1 even when i know the query will return a result? I have tried any possible type of variables and any possible way of inserting into the variable yet no effect.

    DELIMITER $$
    CREATE FUNCTION getShortURLID(URL varchar(1200))
    RETURNS INT 
    DETERMINISTIC
    READS SQL DATA 
    MODIFIES SQL DATA
    BEGIN
        SET @newShortURL:=NULL;
        SELECT SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(URL, "http://", ""), "https://", ""), "www.", ""), '/', 1) INTO @newShortURL ;
    set @URLID:= -1;
    set @URLID := (SELECT ifnull(id,0) FROM url_productivity WHERE url = @newShortURL LIMIT 1);

    IF @URLID <=> 0 then
        INSERT INTO urls(url) VALUES(@newShortURL); 
        RETURN LAST_INSERT_ID();
    ELSE 
        RETURN @URLID ;
    END IF;
    END$$
0

This line

set @URLID := (SELECT ifnull(id,0) FROM url_productivity WHERE url = @newShortURL LIMIT 1);

isn't returning any rows at all, therefore @URLID retains the value set in the preceding line i.e. -1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.