SP_Account_LogIn.sql

CREATE PROCEDURE [Account_LogIn]
    @UserName NVARCHAR(200),
    @Password NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @AspNetUserID NVARCHAR(256), 
            @UserType NVARCHAR(200), 
            @TableID INT, 
            @Name NVARCHAR(200);

    -- 🔹 Verify User Credentials
    SELECT @AspNetUserID = AspNetUserID
    FROM [dbo].[ASP_AspNetUser]
    WHERE UserName = @UserName 
      AND PasswordHash = @Password 
      AND IsDeleted = 0;

    -- 🔹 Exit if User is Not Found
    IF @AspNetUserID IS NULL
    BEGIN
        RETURN;
    END

    -- 🔹 Fetch User Role and Details in a Single Query using UNION ALL
    SELECT TOP 1 
        @UserType = Role.AspNetUserRoleID,
        @TableID = X.TableID,
        @Name = X.Name
    FROM [dbo].[ASP_AspNetUserWiseRole] Role
    JOIN (
        SELECT 1 AS UserType, AdminID AS TableID, Name 
        FROM [dbo].[SEC_Admin] WHERE AspNetUserID = @AspNetUserID AND IsDeleted = 0
        UNION ALL
        SELECT 2, DealerID, UserName 
        FROM [dbo].[DEL_Dealer] WHERE AspNetUserID = @AspNetUserID AND IsDeleted = 0
        UNION ALL
        SELECT 3, ManufacturerID, CompanyName 
        FROM [dbo].[MAN_Manufacturer] WHERE AspNetUserID = @AspNetUserID AND IsDeleted = 0
        UNION ALL
        SELECT 4, WholesalerID, WholesalerName 
        FROM [dbo].[WHO_Wholesaler] WHERE AspNetUserID = @AspNetUserID AND IsDeleted = 0
    ) X ON Role.AspNetUserID = @AspNetUserID;

    -- 🔹 Return User Details
    SELECT 
        @AspNetUserID AS AspNetUserID, 
        @Name AS Name, 
        @UserType AS UserType, 
        @TableID AS TableID;
END