Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: insert row, then update using inserted identity issues..

    hey everyone, I have the following SQL:

    Code:
    CREATE PROCEDURE [dbo].[sp_InsertItem]
    
    	@item_channel_id INT, @item_title VARCHAR(75), @item_link VARCHAR(75),
    	@item_description VARCHAR(150), @item_long_description VARCHAR(1000),
    	@item_date DATETIME, @item_type VARCHAR(20)
    
    AS
    
    IF (@item_type = 'article')
    BEGIN
    	
    	INSERT INTO items (
    
    		item_channel_id, item_title, item_link, item_description, item_long_description,
    		item_date, item_type
    
    	) VALUES (
    
    		@item_channel_id, @item_title, @item_link, @item_description, @item_long_description,
    		@item_date, @item_type
    
    	)
    
    END
    
    IF (@item_type = 'mediaItem')
    BEGIN
    
    	DECLARE @new_identity INT
    	DECLARE @new_link VARCHAR(100)
    
    	INSERT INTO items (
    
    		item_channel_id, item_title, item_link, item_description, item_long_description,
    		item_date, item_type
    
    	) VALUES (
    
    		@item_channel_id, @item_title, @item_link, @item_description, @item_long_description,
    		@item_date, @item_type
    
    	)
    
    	SET @new_identity = @@IDENTITY
    	SET @new_link = @item_link + @new_identity
    
    	UPDATE items
    	SET item_link = @new_link 
    	WHERE item_id = @new_identity	
    
    END
    GO
    Basically, what I am trying to do is this...

    IF the item type is article, insert normally... which works fine...

    however, if the item time is mediaItem, insert part of the item_link... (everything minus id.. eg: site.com/items.aspx?item_id=)... then once the row has been inserted, update that row, to make the link site.com/items.aspx?item_id=<new id>

    however, when the sql runs the mediaItem code, it leaves the item_link field blank.

    Why is this doing this?

    Thanks all!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are you storing the primary key value twice in two separate fields?
    George
    Home | Blog

  3. #3
    Join Date
    Aug 2005
    Posts
    55
    the field item_channel_id is not a primary key...sorry if this is not what you meant, could you please explain?

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •