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)

Using `EXEC` introduces a new batch where the procedure parameters are not in scope. Working around this is not trivial, you need `sp_executesql` and have yet another set of output parameters in the statement itself. Or, you know, don't use dynamic SQL; it seems unnecessary here since you choose between just two tables. Stick these in their own stored procedures and choose at runtime, or have a view that combines both tables with a `UNION`/`UNION ALL` and select from there. — Jeroen Mostert, Dec 07 '20 at 10:21

score 2 · Accepted Answer · answered Dec 07 '20 at 10:45

..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

score 0 · Answer 2 · answered Oct 04 '21 at 15:44

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

This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/29991889) — sanastasiadis, Oct 04 '21 at 20:04

Stored procedure with OUTPUT - Must declare the scalar variable

2 Answers2