Insert the row identity number in another column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Insert the row identity number in another column

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-17 : 04:46:42

Hi,
I am trying to do an insert into a table, and in some fields (columns) I want to insert the row identity number followed by a file name. For instance, if the row identity number is 320, then insert to a field ‘320_imageClock.jpg’. But in my SP it doesn’ t work because the identity is retrieved after the insert operation occurs, so I can’ t join the row identity number with the file name. How can I do it?

Here is my SP:


USE HHA
GO
ALTER PROC upload_Product_image
@Image_name_Q varchar(256) = null, @ Image_name_HQ varchar(256) = null, 
@ Image_name_MQ varchar(256) = null, Image_name_LQ varchar(256) = null,
@Directory_Path_num int = 5, @OfId_Image_name_Q varchar(256) output, 
@OfId_Image_name_HQ varchar(256) output, @OfId_Image_name_MQ varchar(256) output, @OfId_Image_name_LQ varchar(256) output 
AS
  
SET NOCOUNT ON

	INSERT INTO Offers (Image_name_Q, Image_name_HQ, Image_name_MQ, Image_name_LQ, Directory_Path_num)

	VALUES (@OfId_Image_name_Q, @OfId_Image_name_HQ, @OfId_Image_name_MQ, @OfId_Image_name_LQ, @Directory_Path_num)

SET NOCOUNT OFF


Declare @Offer_id As varchar(256)
SET @Offer_id = SCOPE_IDENTITY()

SET @OfId_Image_name_Q =
CASE @Image_name_Q WHEN null THEN null ELSE @Offer_id + '_' + @Image_name_Q END

SET @OfId_Image_name_HQ =
CASE @Image_name_HQ WHEN null THEN null ELSE @Offer_id + '_' + @Image_name_HQ END

SET @OfId_Image_name_MQ =
CASE @Image_name_MQ WHEN null THEN null ELSE @Offer_id + '_' + @Image_name_MQ END

SET @OfId_Image_name_LQ =
CASE @Image_name_LQ WHEN null THEN null ELSE @Offer_id + '_' + @Image_name_LQ END      

GO

Thank you,
Cesar

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-09-17 : 04:57:45

since you're doing this on a per row basis and hopefully you know the seed increment,

set @last=select top 1 IdentityField from upload_product_image
order by IdentityField desc

then you can now concat another field to identity by adding seed increment to @last and saving on another column

hope this helps...

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-17 : 06:03:58

..But, what happens if while the user A is executing the SP, and after he/she has saved the last identity generated (set @last..) and before reaching to make the insert, another user B is also executing the SP and he/she has already generated another row identity number? Then, the user A will has a different row identity number than it saved (@last), in this case the row identity number will be a number more that the saved (@last).. Isn’ t it? And I want the same identity number than saved in ‘@last’.

cesark
Posting Yak Master

215 Posts

Posted - 2004-09-17 : 10:38:27

..And if I do this?:


USE HHA
GO
ALTER PROC upload_Product_image
@Image_name_Q varchar(256) = null, @ Image_name_HQ varchar(256) = null, 
@ Image_name_MQ varchar(256) = null, Image_name_LQ varchar(256) = null,
@Directory_Path_num int = 5, @OfId_Image_name_Q varchar(256) output, 
@OfId_Image_name_HQ varchar(256) output, @OfId_Image_name_MQ varchar(256) output, @OfId_Image_name_LQ varchar(256) output 
AS
  
SET NOCOUNT ON

    INSERT INTO Offers (Directory_Path_num)
    VALUES (@Directory_Path_num)

SET NOCOUNT OFF


Declare @Offer_id As varchar(256)' Originally this field (Offer_id) is bigint. Instead of convert it I declare it as varchar. Is this practice correct?

SET @Offer_id = SCOPE_IDENTITY()

SET @OfId_Image_name_Q =
CASE @Image_name_Q WHEN null THEN null ELSE @Offer_id + '_' + @Image_name_Q END

SET @OfId_Image_name_HQ =
CASE @Image_name_HQ WHEN null THEN null ELSE @Offer_id + '_' + @Image_name_HQ END

SET @OfId_Image_name_MQ =
CASE @Image_name_MQ WHEN null THEN null ELSE @Offer_id + '_' + @Image_name_MQ END

SET @OfId_Image_name_LQ =
CASE @Image_name_LQ WHEN null THEN null ELSE @Offer_id + '_' + @Image_name_LQ END      


SET NOCOUNT ON

UPDATE Offers
SET Image_name_Q = @OfId_Image_name_Q, Image_name_HQ = @OfId_Image_name_HQ, Image_name_MQ = @OfId_Image_name_MQ, Image_name_LQ = @OfId_Image_name_LQ
WHERE Offer_id = @Offer_id

SET NOCOUNT OFF
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources