0

I have found answers to my question, they all state that the below syntax should work. It does not work for me. My output variable is always NULL so any help would be appreciated.

DECLARE @SqlCommand NVARCHAR(1000),
              @ParamDefinition nvarchar(500),
              @Id INT

SET @SqlCommand = N'INSERT INTO [server].db.dbo.table(Title) VALUES(''Insert Into table From othertable Test'')  SELECT @Id = SCOPE_IDENTITY()'
SET @ParamDefinition = N'@Id integer OUTPUT'

EXECUTE sp_executesql @SqlCommand, @ParamDefinition, @Id OUTPUT

Select @Id

My Select @Id returns NULL every time, but the row is inserted into the table.

I found four or five results on Stack Overflow that state my code should work and should return the ID. But it does not.

I am working on a third party server, is there a chance that an error is being thrown that I am not seeing.

Thanks for any help.

Note: The statement below returns the value of the Scope_Identity(). Problem is I have no idea how to catch that value in a variable.

EXEC [Server].[db].[dbo].sp_executesql N'
     INSERT INTO [server].db.dbo.table(Title) VALUES(''Test Title Insert'')
     SELECT SCOPE_IDENTITY()'

I have tried the syntax below with no luck

DECLARE @Id INT

EXEC @Id = [Server].[db].[dbo].sp_executesql N'
     INSERT INTO [server].db.dbo.table(Title) VALUES(''Test Title Insert'')
     SELECT SCOPE_IDENTITY()'

Select @Id - @Id Is always NULL.

Alwelder
  • 1
  • 2

1 Answers1

0

Your parameter definition in the first snippet should use int rather than integer.

In addition, you could try using the OUTPUT keyword as explained in this question.

Community
  • 1
  • 1
Dan Def
  • 1,836
  • 2
  • 21
  • 39