Thread: Variable Scope?

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    5
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    Why don't you show the real code?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    5
    Rep Power
    0
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    No es importante
    Posts
    2,065
    Rep Power
    14
    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.
    El éxito consiste en una serie de pequeñas victorias día a día

    MySQL, MS SQL, MS ACCESS, Oracle Database Manager - http://victorpendleton.net/products/psdviewer.html
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    5
    Rep Power
    0
    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.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2003
    Location
    Paris Uppland
    Posts
    2,385
    Rep Power
    391
    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
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    5
    Rep Power
    0
    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.

IMN logo majestic logo threadwatch logo seochat tools logo