Variable Scope?

Hi,

I'm new to sql, so this will probably be pretty obvious ... When I execute the below code it always returns 000. What am I doing wrong? Is it to do with the scope of the variable changing within the BEGIN END?

-------------------------

DECLARE @VariableName INT

SET @VariableName = 000

IF ConditionBlaBla
BEGIN
SET @VariableName = 123
END
ELSE
BEGIN
SET @VariableName = 456
END

SELECT @VariableName 'ReturnName'

------------------------------

The variable is a primary key for a record. The condition is: If 0 is passed to the stored procedure then add a new record and set the variable to @@identity else update the record based on the variable passed in. The return value is either the existing Id or the new Id generated from inserting the new record.

There are also some additional variables set within the IF and ELSE.

Thanks.

Why don't you show the real code?

Here's the entire stored proc. I'm not the original author, I'm doing some maintenance and bug fixes on the site. The crux of the problem is the returning of the New @ContentId when it gets created in the SiteContent table. The line 'SET @ContentId = @@Identity' doesn't return the new value, it keeps the original value and I suspect its related to the fact that the line 'SET @ContentId = @@Identity' is executed within a BEGIN END structure. Am I on the right track?

---------------------
CREATE Procedure dbo.SiteAddContentSub
@PageId int,
@ContentId int = 0,
@Title nvarchar(150),
@ContentTypeId int = 1,
@ContentStatusId int = 1,
@Comments nvarchar(400) = '',
@Body ntext = '',
@Searchable bit = 1,
@DateExpires varchar(20) = '',
@DatePublish varchar(20) = '',
@Guid varchar(100),
@Active int = 1,
@ImageId int

AS

SET NOCOUNT ON
SET DATEFORMAT dmy

DECLARE @ErrorReturn INT
DECLARE @Status INT
DECLARE @AuditId INT
DECLARE @OldContentStatusId INT
DECLARE @NewContentId INT
DECLARE @ContentSubId INT

SET @OldContentStatusId = 0

BEGIN TRANSACTION

-- step one, who is making this change
SELECT
@AuditId = UserId
FROM
UserSession
WHERE
SessionId LIKE '' + @Guid + ''

-- if no auditor, then don't make the change.
IF @AuditId > 0
BEGIN
IF @DateExpires = ''
BEGIN
SET @DateExpires = Null
END

IF @DatePublish = ''
BEGIN
SET @DatePublish = Null
END

SELECT
@OldContentStatusId = ContentStatusId
FROM
SiteContent
WHERE
id = @ContentId

-- If they are the same then don't need to add a record in SiteContent
IF @OldContentStatusId <> @ContentStatusId
BEGIN
SELECT
@NewContentId = Contentid
FROM
dbo.vwSiteContent sc
WHERE
ContentStatusId = @ContentStatusId AND
PageId = @PageId

-- if a record allready exists, then don't need to add a record in Site Content
IF @NewContentId > 0
BEGIN
SET @ContentId = @NewContentId
END
ELSE
BEGIN
INSERT INTO SiteContent
(
PageName,
Title,
CreatedDate,
UpdatedDate,
ContentStatusId,
ContentTypeId,
Searchable,
Active,
Expires,
PublishDate,
ImageId
)
SELECT
PageName,
@Title,
getdate(),
getdate(),
@ContentStatusId,
@ContentTypeId,
@Searchable,
@Active,
@DateExpires,
@DatePublish,
@ImageId
FROM
SitePage sp
WHERE
sp.id = @PageId

SET @ContentId = @@Identity

SELECT @ErrorReturn = @@Error
IF @ErrorReturn <> 0 GOTO Error_Catcher

-- put an entry into the join table
INSERT INTO SitePageContent
(
PageId,
ContentId
)
VALUES
(
@PageId,
@ContentId
)

SELECT @ErrorReturn = @@Error
IF @ErrorReturn <> 0 GOTO Error_Catcher
END
END

-- Now insert a record into Sub table
INSERT INTO SiteContentSub
(
ContentId,
Title,
Content,
Comments,
CreatedDate,
UpdatedDate,
ContentStatusId,
ContentTypeId,
Searchable,
Active,
Expires,
PublishDate,
ImageId,
NavOrder
)
SELECT
@ContentId,
@Title,
@Body,
@Comments,
getdate(),
getdate(),
@ContentStatusId,
@ContentTypeId,
@Searchable,
@Active,
@DateExpires,
@DatePublish,
@ImageId,
ISNULL((SELECT Max(NavOrder)FROM SiteContentSub WHERE ContentId =@ContentId), 0) + 1

SELECT @ContentSubId = @@Identity

SELECT @ErrorReturn = @@Error
IF @ErrorReturn <> 0 GOTO Error_Catcher

-- store the id as a LinkTo column. This is used as a reference to allow items to be linked to each other for updates.
UPDATE
SiteContentSub
SET
IsLinkedTo = @ContentSubId
WHERE
id = @ContentSubId


SELECT @ErrorReturn = @@Error
IF @ErrorReturn <> 0 GOTO Error_Catcher

-- insert audit record
INSERT
Audit(AuditTypeId, OldStatusId, NewStatusId, AuditUserId, ChangedItemId, AuditDate)
SELECT
2, @ContentStatusId, @ContentStatusId, @AuditId, @ContentId, getdate()

SELECT @ErrorReturn = @@Error
IF @ErrorReturn <> 0 GOTO Error_Catcher


COMMIT TRANSACTION
SET @Status = 1
SELECT @Status 'Status', @ContentId 'Id', @ContentSubId 'SubId'

END

Error_Catcher:

IF @ErrorReturn <> 0
BEGIN
ROLLBACK
SET @Status = 0
SELECT @Status 'Status', @ErrorReturn 'Id'
END
GO

Couple of test:
Print out the value of @@Identity

What is the auto incrementing field?
Capture the MAX value of this field and print out.

You may be losing the IDENTITY value in the INSERT INTO SELECT statement.

I have checked the tables after the proc is exec and found that a New record is inserted fine into SiteContent it gets the new Id correctly and inserts the record into SitePageContent correctly too. So the variable @ContentId does have the correct value at that point ... its only after that when it attempts it insert into SiteContentSub that it fails. By 'Fail' I mean, the ContentId inserted into SiteContentSub is 0? I have also tried hardcoding @ContentId to = 123 and it inserted into the table fine.

Each of the tables all have a Primary Key identity column called 'id'

Thanks for your help.

Do you have any insert triggers on SiteContentSub ?

If there is a trigger that does an insert on another table with identity property that should mess things up. There are other functions for getting the identity value that takes triggers into considieration.

Look up scope_identity in Books on Line

There are no triggers on SiteContentSub swampboogie.

I've managed to get around the problem by doing the following.

Straight after the insert into SitePageContent I then get the @@identity and select the contentid back out of that table and set it to the variable @ContentId again. ... it gets the contentid correctly and inserts it into SiteContentSub fine. :-)

Thanks for all your help & suggestions

Shannon.