Troubles pulling id from first insert done by stored procedure to do use on second stored procedure

Question

I'm having troubles pulling the id from the first insert to use on the second insert. Here's my SQL (I'm using stored procedures):

DECLARE @JoinDate date
DECLARE @ID int
SET @JoinDate = getdate()

EXEC Members_Add $(UserID), '$(UserName)', 
       @JoinDate, '$(firstname)', '$(lastname)', NULL, 
      '$(Country)', NULL,  '$(stateorprovince)', '$(city)', 
      '$(ziporpostalcode)', '$(addressline1)', '$(addressline2)', 
      '$(MailCountry)', NULL, '$(mailstateprovince)', '$(MailCity)', 
      '$(mailzipcode)', '$(mailaddress)', NULL, NULL, NULL, 
      '$(mobilephone)', NULL, '$(Fax)', '$(Email)', NULL, NULL

SELECT @ID = SCOPE_IDENTITY()

EXEC Merchants_Add @ID, NULL, '$(BusinessName)', '$(CorporateName)', 
      '$(contactperson)', '$(OfficePhone)', '$(website)', 
      '$(DirectoryListing)', 'False'

I need to get the ID of the record added by the first stored procedure, I read up that you should use SELECT @@IDENTITY instead of SELECT Max(ID) but it doesn't seem to be working...

EDIT: I just updated the SELECT @@IDENTITY AS NEW_ID to SELECT SCOPE_IDENTITY AS NEW_ID and now I'm getting a cannot convert nvarchar to int error... any ideas?

EDIT #2: Updated the code again... now I'm getting cannot insert the vaule NULL into column 'MemberID' that's the one that @ID is in for the Merchants_Add procedure.

Answer 1

Try:

DECLARE @JoinDate date
DECLARE @newId int
SET @JoinDate = getdate()

EXEC Members_Add $(UserID), '$(UserName)', @JoinDate, '$(firstname)', '$(lastname)', NULL, '$(Country)', NULL, '$(stateorprovince)', '$(city)', '$(ziporpostalcode)', '$(addressline1)', '$(addressline2)', '$(MailCountry)', NULL, '$(mailstateprovince)', '$(MailCity)', '$(mailzipcode)', '$(mailaddress)', NULL, NULL, NULL, '$(mobilephone)', NULL, '$(Fax)', '$(Email)', NULL, NULL

SELECT @newId = SCOPE_IDENTITY()

EXEC Merchants_Add @newId, NULL, '$(BusinessName)', '$(CorporateName)', '$(contactperson)', '$(OfficePhone)', '$(website)', '$(DirectoryListing)', 'False'

EDIT: Changed source to reflect needed syntax using the preferred SCOPE_IDENTITY() instead of @@IDENTITY.

Answer 2

You should use SCOPE_IDENTITY().

Have a look at SCOPE_IDENTITY (Transact-SQL)

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

Try using something like this

DECLARE @ID INT
EXEC Members_Add $(UserID), '$(UserName)', 
       @JoinDate, '$(firstname)', '$(lastname)', NULL, 
      '$(Country)', NULL,  '$(stateorprovince)', '$(city)', 
      '$(ziporpostalcode)', '$(addressline1)', '$(addressline2)', 
      '$(MailCountry)', NULL, '$(mailstateprovince)', '$(MailCity)', 
      '$(mailzipcode)', '$(mailaddress)', NULL, NULL, NULL, 
      '$(mobilephone)', NULL, '$(Fax)', '$(Email)', NULL, NULL

SELECT @ID = SCOPE_IDENTITY()

EXEC Merchants_Add @ID, NULL, '$(BusinessName)', '$(CorporateName)', 
      '$(contactperson)', '$(OfficePhone)', '$(website)', 
      '$(DirectoryListing)', 'False'

Note the use of the @ID

From @@IDENTITY

Answer 3

You need to declare and use a T-Sql variable to hold the identity value. And you should generally use Scope_Identity() not @@Identity...

  DECLARE @JoinDate dateSET @JoinDate = getdate()
  Declare @NewId Integer 
  EXEC Members_Add $(UserID), '$(UserName)',  @JoinDate, '$(firstname)', 
       '$(lastname)', NULL, '$(Country)', NULL,  '$(stateorprovince)',      
       '$(city)',  '$(ziporpostalcode)', '$(addressline1)',        
       '$(addressline2)','$(MailCountry)', NULL, '$(mailstateprovince)', '
       $(MailCity)','$(mailzipcode)', '$(mailaddress)', NULL, NULL, NULL,
       '$(mobilephone)', NULL, '$(Fax)', '$(Email)', NULL, NULL

  -- Here get identity Value
  Set @NewId = Scope_Identity()

  EXEC Merchants_Add @NewId, NULL, '$(BusinessName)', '$(CorporateName)',
       '$(contactperson)', '$(OfficePhone)', '$(website)',
       '$(DirectoryListing)', 'False'