Stored Procedure

[aabruzzese]

Hi,

I am modifying a stored proc to add a value to a table which results from a Select MAX from another Table.

#1.. Insert a Blank Dummy record to be updated at a later time

INSERT INTO dbo.images
(title, 
largeimage, 
thumbimage, 
origimage, 
album, 
notes)
VALUES (NULL, 
NULL, 
NULL, 
NULL, 
9999, 
NULL)
 
#2.. If the Insert worked pull the AutoIncrement Filed Id from the table 
 
IF( @@ERROR = 0 )
BEGIN
SELECT Max(id)as @ImageId from dbo.images 
END 
 
#3.. Then later insert the value @ImageId into the desired Table
 
INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart,
ImageId )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart,
@ImageId ) 

So my question is what should i define the @ImageId as within the Stored Procedure, also the Stored Procedure returns an error with the Select Statement as coded.

So how do I assign the Value of SELECT Max(id) from dbo.images to a Variable that can then be used in my Insert Command?

[dmj07]

You should use:

SET @ImageID = SCOPE_IDENTITY()

This will return the auto increment file id after the first insert

[rpicilli]

Hi there,


The very first action you must take is declare the variable that will receive the id from the table

DECLARE @ImageID int

Hope this help you


Rpicilli