"Must declare variable", but when I declare it, "variable already declared"

ado.net c# dapper sql-server

Question

I'm generating insert SQL which returns the primary key to the caller. The SQL is generated for an arbitrary table, so I can't use SCOPE_IDENTITY() because not every table uses an identity. When I execute my query, MS SQL complains the output var is not declared, but says it's already declared when I declare it. Damned if I do, damned if I don't.

var parameters = new DynamicParameters();
parameters.Add("d", direction:ParameterDirection.Output, size:8000);
// (Add the input params a, b, c)
remoteConnection.Execute(sql, parameters);

SQL:

-- Error: Must declare the table variable "@d"
INSERT INTO Blacklist
(DatabaseConnectionId,TableSchema,TableName)
OUTPUT INSERTED.Id INTO @d
VALUES (@a,@b,@c)

I declare the variable:

DECLARE @d int
INSERT INTO Blacklist
(DatabaseConnectionId,TableSchema,TableName)
OUTPUT INSERTED.Id INTO @d
VALUES (@a,@b,@c)
-- Error: The variable name '@d' has already been declared. Variable names must be unique within a query batch or stored procedure.

How do I do this?

Accepted Answer

You need to declare @d as a TABLE variable:

DECLARE @d TABLE
(
    id INT
);

INSERT INTO Blacklist
(DatabaseConnectionId,TableSchema,TableName)
OUTPUT INSERTED.Id INTO @d
VALUES (@a,@b,@c)


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why