1

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?

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
Charles Burns
  • 10,310
  • 7
  • 64
  • 81

1 Answers1

1

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)
Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541