[RESOLVED] Not getting the out variable when doing an update

[r0k3t]

Hi there,

I have an upsert that returns the ID of the row created when doing and insert however I can't seem to get it to return the value of the row it updated! I know it updates... I checked the DB.

I have monkied with some and figured I would just post the proc and someone could point out there error... I know the set statement is working cause it is updating like I just was saying. I have a proc very similar to this that updates the contact and it works.

GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'UpsertAddress')
BEGIN
PRINT 'Dropping Procedure UpsertAddress'
DROP Procedure UpsertAddress
END
GO

PRINT 'Creating Procedure UpsertAddress'
GO
CREATE Procedure UpsertAddress
@ContactId int,
@EventId int,
@AddressTypeId int,
@Address1 varchar(50),
@Address2 varchar(50) = null,
@Address3 varchar(50) = null,
@City varchar(50),
@State varchar(50),
@PostalCode varchar(50),
@CountryRegionId int,
@Latitude varchar(20) = null,
@Longitude varchar(20) = null,
@MapUrl varchar(255) = null,
@Id int OUTPUT

AS
BEGIN
    SET NOCOUNT ON;
   

    IF EXISTS (SELECT Id FROM [dbo].Address (NOLOCK) WHERE ContactId=@ContactId 
															AND AddressTypeId = @AddressTypeId
															AND EventId = @EventId)
     BEGIN
		SET @Id = (SELECT Id
	             FROM [dbo].[Address] (NOLOCK)
				WHERE ContactId=@ContactId 
	            AND AddressTypeId = @AddressTypeId
				AND EventId = @EventId)

        UPDATE [dbo].Address SET
				Address1 = @Address1,
				Address2 = @Address2,
				Address3 = @Address3,
				City = @City,
				State = @State,
				PostalCode = @PostalCode,
				CountryRegionId = @CountryRegionId,
				Latitude = @Latitude,
				Longitude = @Longitude,
				MapUrl = @MapUrl,
				EventId = @EventId
		WHERE Id = @Id
--          WHERE ContactId = @ContactId 
--			AND AddressTypeId = @AddressTypeId
--			AND EventId = @EventId
      END
    ELSE
      BEGIN
        INSERT INTO [dbo].Address
            (
				ContactId,
				AddressTypeId,
				Address1,
				Address2,
				Address3,
				City,
				State,
				PostalCode,
				CountryRegionId,
				Latitude,
				Longitude,
				MapUrl,
				EventId
			)
        VALUES
            (
				@ContactId,
				@AddressTypeId,
				@Address1,
				@Address2,
				@Address3,
				@City,
				@State,
				@PostalCode,
				@CountryRegionId,
				@Latitude,
				@Longitude,
				@MapUrl,
				@EventId
			)
		SELECT @ID = @@IDENTITY
  END
END

GO
GRANT EXEC ON UpsertAddress TO PUBLIC
GO

[r0k3t]

Weird - I just reran this and now it works... I know this proc has been out there for a while too. Uhg! I hate mystery errors.