February 9th, 2003, 10:35 PM
-
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.
February 10th, 2003, 02:31 AM
-
Why don't you show the real code?
February 10th, 2003, 03:47 PM
-
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
February 10th, 2003, 09:44 PM
-
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.
February 10th, 2003, 10:11 PM
-
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.
February 11th, 2003, 03:02 AM
-
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
February 11th, 2003, 06:54 PM
-
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.