Stored procedure with OUTPUT - Must declare the scalar variable

Question

I'm writing a stored procedure that will be executed from C# to get data from database. Therefore I have to pass a GUID to this stored procedure and it should find data in table Contact or in the Lead table & return data back to C# app via output parameters.

When I try to execute this stored procedure in SSMS, I get a SQL exception

Must declare the scalar variable "@LastName"

Code:

ALTER PROCEDURE [api].[GetUser_NetId]
      @NetId uniqueidentifier
    , @LastName nvarchar(200) = '' OUTPUT
    , @FirstName nvarchar(200) = ''  OUTPUT
    , @Country uniqueidentifier = NULL  OUTPUT
    , @Newsletter bit = 0  OUTPUT
AS
    DECLARE
        @Table      SMALLINT

    SET @Table = (
        SELECT MIN(T.ID) FROM (
            SELECT 100 AS [ID] FROM dbo.Contact WHERE Net_ID = @NetId
            UNION ALL
            SELECT 200 AS [ID] FROM dbo.Lead WHERE Net_ID = @NetId
            ) T
        )

    DECLARE @SQL NVARCHAR(MAX)

    SET @SQL = CONCAT(
'   SELECT 
        @LastName = tbl.LastName,
        @FirstName = tbl.FirstName,
        @Country = tbl.Address1CountryId,
        @Newsletter = tbl.Newsletter,
    FROM
        dbo.'
    , CASE @Table
        WHEN 100 THEN 'Contact'
        WHEN 200 THEN 'Lead'
    END
    , ' as tbl
    WHERE 1=1
        AND tbl.Net_Id = '''
    , @NetId
    , '''' 
    )

    EXEC(@SQL)

Answer 1

..a slightly simpler approach

ALTER PROCEDURE [api].[GetUser_NetId]
      @NetId uniqueidentifier
    , @LastName nvarchar(200) = '' OUTPUT
    , @FirstName nvarchar(200) = ''  OUTPUT
    , @Country uniqueidentifier = NULL  OUTPUT
    , @Newsletter bit = 0  OUTPUT
AS
BEGIN
    
    IF EXISTS(SELECT * FROM dbo.Contact WHERE Net_ID = @NetId)
    BEGIN
        SELECT 
            @LastName = tbl.LastName,
            @FirstName = tbl.FirstName,
            @Country = tbl.Address1CountryId,
            @Newsletter = tbl.Newsletter
        FROM dbo.Contact WHERE Net_ID = @NetId;
    END
    ELSE
    BEGIN
        SELECT 
            @LastName = tbl.LastName,
            @FirstName = tbl.FirstName,
            @Country = tbl.Address1CountryId,
            @Newsletter = tbl.Newsletter
        FROM dbo.Lead WHERE Net_ID = @NetId;
    END
END