How to avoid local variable and subquery to return child records from child table

Transact-SQL
1

I have the stored procedure in MS SQL SERVER. I have two tables a and b. The B table has the foreign key AId to the table A to its primary key Id. I need to return records from parent table A filtered by name and and the same time the records from the child table that're assosiated by its id record of table A. I implemented as I listed. Is there any way to avoid using declare and the subquery?

CREATE PROCEDURE [dbo].[sp_GetByName](@name NVARCHAR(255))
AS BEGIN
    SELECT
        [a].[Id],
        [a].[Name]
    FROM [dbo].[a]
    WHERE [a].[Name] = @name;
    
    DECLARE @aId INT = (SELECT TOP (1) [Id] FROM [dbo].[a] WHERE [Name] = @name);

    SELECT
        [b].[Id],
        [b].[AId],
        [a].[Name] as AccountName
    FROM [dbo].[b] LEFT JOIN [dbo].[a] ON [b].[AId] = [a].[Id]
    WHERE [ag].[AccountGroupId] = @aId;
END;
GO;
2

Alias "ag" is not defined in your query, but other than that you can do this like so:


CREATE PROCEDURE [dbo].[sp_GetByName](@name NVARCHAR(255))
AS BEGIN
    SELECT
        [a].[Id],
        [a].[Name]
    FROM [dbo].[a]
    WHERE [a].[Name] = @name;

    SELECT
        [b].[Id],
        [b].[AId],
        [a].[Name] as AccountName
    FROM [dbo].[b] LEFT JOIN [dbo].[a] ON [b].[AId] = [a].[Id]
    WHERE [ag].[AccountGroupId] = ( --<<-- "ag" is not defined
        SELECT TOP (1) [Id]
        FROM dbo.[a]
        WHERE [a].[Name] = @name
        )   
END;
GO