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

Answer 2

I am getting an error like:

Msg 137, Level 15, State 2, Line 18
Must declare the scalar variable "@CustomerKey".
Msg 137, Level 15, State 1, Line 21
Must declare the scalar variable "@FirstName".
Msg 137, Level 15, State 1, Line 30
Must declare the scalar variable "@FirstName".

IF EXISTS(SELECT * FROM VW_FactInternetSales  WHERE CustomerKey = @CustomerKey)
    BEGIN   
        SELECT
        @FirstName = .FirstName,
        @TaxAmt = .TaxAmt,
        @Country = .Country,
        @CustomerKey = .CustomerKey
        FROM DimCustomer WHERE CustomerKey = @CustomerKey
        END
        ELSE
        BEGIN
            SELECT
            @FirstName = .FirstName,
            @TaxAmt = .TaxAmt,
            @Country = .Country,
            @CustomerKey = .CustomerKey
            FROM VW_FactInternetSales WHERE CustomerKey = @CustomerKey
        END
    END

I cant add my table in this line

@FirstName = .FirstName,
        @TaxAmt = .TaxAmt,
        @Country = .Country,
        @CustomerKey = .CustomerKey