带OUTPUT的存储过程-必须声明标量变量

Question

我正在编写一个存储过程，该过程将从C#执行以从数据库获取数据。因此，我必须将GUID传递给这个存储过程，并且它应该在表Contact或中的Lead表&通过输出参数将数据返回到C#应用程序。

当我尝试在SSMS中执行这个存储过程时，我会得到一个SQL异常

必须声明标量变量"姓氏"；

代码：

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

。。一种稍微简单一点的方法

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

我收到一个错误，如：

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

这行我不能加我的表

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