You must Sign In to post a response.
  • Category: .NET

    How to get multiple output parameters value from stored procedure

    I am writing stored procedure for login in which i want result through integer output parameters usertype and loginstatus as 1 and 1 respectively.

    Following is stored procedure code:

    CREATE procedure [dbo].[usp_login]
    (@userid nvarchar(50), @password nvarchar(50), @usertype int output, @loginstatus int output)
    As

    Begin

    if exists(Select COUNT(User_Id) from User_Master where User_Id = @userid and Password = @password)

    begin
    select @usertype = User_Type from User_Master where User_Id = @userid and Password = @password;
    set @loginstatus = 1;
    end
    else
    begin
    set @usertype = 0
    set @loginstatus = 0;
    end
    End

    I am executing the sp in ssms as follows:

    declare @usrtype int;
    declare @lgnstat int;

    exec usp_login 'a', 'a', @usrtype output, @lgnstat output

    print @usrtype
    print @lgnstat

    Now when i pass correct userid password i get result as 1,1 which is as expected.
    But, when i pass wrong userid or password i get result as 1 which is expected as 0,0.

    Please tell me where's the problem either in sp definition code or execution code.
  • #768837
    Hi,

    I don't understand what is purpose of if Exists condition there,
    Better you can use the count function itself like below.


    Declare @intvar as Integer
    Select @intvar = COUNT(User_Id) from User_Master where User_Id = @userid and Password = @password

    If (@intvar > 0)
    Begin
    set @loginstatus = 1;
    END
    Else
    Begin
    set @usertype = 0
    set @loginstatus = 0;
    End



    Kindly try this.

    Thanks,
    Mani

  • #768842
    Hi,

    I guess the problem is if condition, may be it's not working properly in all cases, as suggested by Manigandan you can try to get the count of the records first and then check the count in condition, that will help you to give exact output.

    Hope this helps you...

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments