problem with multi statement table valued function, what am I doing wrong?

Question

Hi guys, I having problems with this function, seems like @idUsuario and @passCorrecto aren't getting any value, so, when I use this variables in the where clause I'm not getting any result data.

ALTER FUNCTION [dbo].[login](@usuario varchar(20), @password varchar(20))

RETURNS @info TABLE (nombre varchar(70) not null, tipo varchar(30) not null)
AS
BEGIN
    DECLARE @idUsuario int = dbo.usuarioExiste(@usuario)
    DECLARE @passCorrecto bit = dbo.passwordCorrecto(@idUsuario, @password) 

    INSERT @info
        SELECT
            usuarios.nombreUsuario, tiposUsuarios.tipoUsuario
        FROM 
            usuarios
        LEFT JOIN
            tiposUsuarios
        ON
            usuarios.idTipoUsuario = tiposUsuarios.idTipoUsuario
        WHERE
            usuarios.idUsuario = @idUsuario and              
            usuarios.estatus = 'ACTIVO' and
            @passCorrecto = 1
    RETURN 

END

What am I doing wrong?

EDIT

Here are the function used above:

ALTER FUNCTION [dbo].[usuarioExiste]
(
    @usuario varchar(20)
)
RETURNS integer
AS
BEGIN

    DECLARE @idUsuario integer

    SELECT
        @idUsuario = idUsuario
    FROM
        usuarios
    WHERE
        usuario = @usuario

    if @idUsuario is null begin
        set @idUsuario = 0
    end

    RETURN @idUsuario

END


ALTER FUNCTION [dbo].[passwordCorrecto]
(
    @usuario varchar(20),
    @password varchar(20)
)
RETURNS bit
AS
BEGIN

    DECLARE @esCorrecto bit

    SELECT
        @esCorrecto = case when password = @password then 1 else 0 end
    FROM
        usuarios
    WHERE 
        usuario = @usuario

    RETURN @esCorrecto

END

EDIT 2

As suggested by Beth, I created new functions that returns the values that I need like this:

CREATE FUNCTION [dbo].[usuarioExisteTest]
(
    @usuario varchar(20)
)
RETURNS int
AS
BEGIN

    declare @idUsuario int;
    set @idUsuario = 1;

    return (@idUsuario);

END;

By doing this I'm getting the data I need, am I setting the values to return the wrong way in the original functions?

DECLARE @idUsuario integer

        SELECT
            @idUsuario = idUsuario
        FROM
            usuarios
        WHERE
            usuario = @usuario

Answer 1

I know SQL Server 2008 supports the combined DECLARE/assign, but have you tried separating them?

DECLARE @idUsuario int
SET idUsuario = dbo.usuarioExiste(@usuario)
DECLARE @passCorrecto bit 
SET passCorrecto = dbo.passwordCorrecto(@idUsuario, @password) 

From BOL

Assigns a value to the variable in-line. The value can be a constant or an expression, but it must either match the variable declaration type or be implicitly convertible to that type.

Of course, a udf is an expression, but is it possible there is some anomaly when used in DECLARE (like when you use a udf in CHECK constraint: it's not reliable). Or DECLARE ins a udf for some reason.

Please humour me. Try it.

Also, you have no "failsafe" inside dbo.passwordCorrecto which means it could return NULL which will always evaluate to false in the outer udf.

Answer 2

try printing the values of the variables after you declare them. If they aren't set correctly, try calling the functions in a new query window instead of within [dbo].[login]. That should help identify the problem.

Answer 3

I've found a solution, seem like using select @var = some_value doesn't work well. See this:

A SELECT statement that contains a variable assignment cannot be used to also perform typical result set retrieval operations.

These are the fixed functions: usuarioExiste

CREATE FUNCTION [dbo].[usuarioExiste]
(
    @usuario varchar(20)
)
RETURNS int
AS
BEGIN

    declare @idUsuario int = 0;

    set @idUsuario = (
        select
            idUsuario
        from
            usuarios
        where
            usuario = @usuario);

    if @idUsuario is null begin
        set @idUsuario = 0;
    end;

    return (@idUsuario);

END;

passwordCorrecto

CREATE FUNCTION [dbo].[passwordCorrecto]
(
    @idUsuario int,
    @password varchar(20)
)
RETURNS bit
AS
BEGIN

    declare @esCorrecto bit = 'false';

    set @esCorrecto = (
        select
            case when password = @password then 'true' else 'false' end
        from
            usuarios
        where 
            usuarios.idUsuario = @idUsuario);

    return (@esCorrecto);

END;

and login

CREATE FUNCTION [dbo].[login](@usuario varchar(20), @password varchar(20))

RETURNS @info TABLE (nombre varchar(70) not null, tipo varchar(30) not null)
AS
BEGIN

    DECLARE @idUsuario int = dbo.usuarioExistetest(@usuario);

    DECLARE @passCorrecto bit = dbo.passwordCorrectotest(@idUsuario, @password);

    INSERT @info
        SELECT
            usuarios.nombreUsuario, tiposUsuarios.tipoUsuario
        FROM 
            usuarios
        LEFT JOIN
            tiposUsuarios
        ON
            usuarios.idTipoUsuario = tiposUsuarios.idTipoUsuario
        WHERE
            usuarios.idUsuario = @idUsuario and
            usuarios.estatus = 'ACTIVO' and
            @passCorrecto = 'true';
    RETURN 

END;