Error of variables declared in a stored procedure

Navigation
0

I have this procedure that I use to choose between one type of user or another for a login window, I get syntax error but I can not find the logic failure. 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE Sp_Login
-- Add the parameters for the stored procedure here
@usuario varchar(50),
@password varchar(50),
@id int output,
@inst varchar(10)output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
@id = (SELECT Id_Usuario FROM Usuarios WHERE CodigoUsuario = @usuario AND Password = @password)
if @id!=-1
begin
    @inst = 'true'
    print 'true'
end
else
begin
    @id =(SELECT  Id_Administrador FROM Administradores WHERE Usuario=@usuario AND Password = @password)
    if @id!=-1
    begin
        @inst =  'false'
        print 'false'
    end
    else
    begin
        @inst =  'none'
        print 'none'
    end
end
END
GO

UserCode and User never have the same name

The errors of the procedure are in the Insert statements, in @id and @inst

    
asked by OniNeit 21.09.2017 в 11:14
source

4 answers

0

In the end these were the modifications: 

set @id = (SELECT Id_Usuario FROM Usuarios WHERE CodigoUsuario = @usuario AND Password = @password)
if @id!=-1
begin
    set @inst = 'true'
end
else
begin
    set @id =(SELECT  Id_Administrador FROM Administradores WHERE Usuario=@usuario AND Password = @password)
    if @id!=-1
    begin
        set @inst =  'false'
    end
    else
    begin
        set @inst =  'none'
    end
end

I hope that if something similar happens to someone, this will be helpful

    
answered by 21.09.2017 / 18:51
source
1

Variable assignments in SQL SERVER are made using the SET clause or directly from a SELECT . In your case you should replace those instructions: 

@id = (SELECT Id_Usuario FROM Usuarios WHERE CodigoUsuario = @usuario AND Password = @password)
@id = (SELECT  Id_Administrador FROM Administradores WHERE Usuario=@usuario AND Password = @password)

for 

SELECT @id = Id_Usuario FROM Usuarios WHERE CodigoUsuario = @usuario AND Password = @password
SELECT @id = Id_Administrador FROM Administradores WHERE Usuario=@usuario AND Password = @password

And on the other hand, this assignment @inst = 'true' and the others similar, you should replace them with SET @inst = 'true' or possibly SELECT @inst = 'true'

    
answered by 21.09.2017 в 16:13
1

To assign a value to a variable you must do it using the SET instruction as you have already been told. On the other hand, if the subquery does not retrieve rows, the resulting value will not be -1, it will be NULL.

Try the following code: 

CREATE PROCEDURE Sp_Login
@usuario  varchar(50),
@password varchar(50),
@id       int OUTPUT,
@inst     varchar(10) OUTPUT
AS
BEGIN    
SET NOCOUNT ON;

SET @id = (SELECT Id_Usuario FROM Usuarios 
   WHERE CodigoUsuario = @usuario AND [Password] = @password);

IF @id IS NOT NULL SET @inst = 'true';            
ELSE
BEGIN
    SET @id = (SELECT Id_Administrador FROM Administradores
      WHERE Usuario = @usuario AND [Password] = @password);

   IF @id IS NOT NULL        
        SET @inst = 'false';            
    ELSE        
        SET @inst = 'none';            
END

PRINT @inst;
END
GO
    
answered by 21.09.2017 в 16:48
0

I think the problem is the way to assign the @id values, I'm going to change the ones of the select that you use to assign in the following way: 

SELECT  Id_Administrador into @id FROM Administradores WHERE Usuario=@usuario AND Password = @password;

On the other hand, I am not very clear about the syntax of the if you use, in the page of the manual you can see the necessary syntax

    
answered by 21.09.2017 в 11:21
mysql order per day of the week Divide 2 values taken from the Json with AngularJS