Named Query MSSQL Scope Identity Fails

Ignition Early Access
1

Hi all,

I am having trouble getting the scope identity of a newly inserted row in a named query. This was working before using the following:

IF EXISTS (SELECT TagId, TagPath FROM Tags WHERE TagPath = :tagPath AND TagProvider = :tagProvider) 
BEGIN
SELECT TagId FROM Tags WHERE TagPath = :tagPath AND TagProvider = :tagProvider
END
ELSE
BEGIN
INSERT INTO Tags (TagPath, TagProvider, DateAdded, Enabled) VALUES (:tagPath, :tagProvider, GETDATE(), 1)
SELECT SCOPE_IDENTITY()
END

That started to malfunction, so I switched to using this instead:

IF EXISTS (SELECT TagId, TagPath FROM Tags WHERE TagPath = :tagPath AND TagProvider = :tagProvider) 
BEGIN
SELECT TagId FROM Tags WHERE TagPath = :tagPath AND TagProvider = :tagProvider
END
ELSE
BEGIN
INSERT INTO Tags (TagPath, TagProvider, DateAdded, Enabled) VALUES (:tagPath, :tagProvider, GETDATE(), 1)
SELECT TagId FROM Inserted
END

The named query in all cases is set to “scalar query”. Both queries seem to work intermittently, but I can’t determine what makes one work and the other not work in certain cases. Is there a preferred way to do this with named queries?

The alternative is to insert a randomly generated uuid and use that as the identifying value instead of the id value so this stops failing, but that feels like an extra unnecessary column that I’d like to avoid.

I’m in Ignition 8.0.6 and had never seen this behavior until now but I can’t tell if it’s an Ignition issue or a SQL issue. Thoughts?

Cheers,
Roger