[Query - General] Stored PrRodcedure Problem

Hi,



Is it possible to write a stored procedure which uses a Select statement and then Inserts the result of that select statement into a particular field as a new record, all in the same stored procedure?



Here 's my sp currently:



CREATE PROCEDURE [dbo].[usp_AddStaffEvent]

@StaffEventID int,

@StaffNo varchar (6),

@QualID varchar(10),

@Resit varchar(1),

@Submitted datetime,

@StudyOptions varchar(255),

@Aids varchar(255),

@Venue1 int,

@Venue2 int

AS

Insert into StaffEvent (StaffEventID, RLStaffNo, QualID, Resit, DateSubmitted, StudyOptions, Aids, VenueID1, VenueID2)

Values (MaxID, @StaffNo, @QualID, @Resit, @Submitted, @StudyOptions, @Aids, @Venue1, @Venue2)

GO



but I want to add



SELECT Max(StaffEvent(ID)) as MAXID FROM StaffEvent



and insert the result of this select into the first field (StaffEventID) shown above.



Thanks.

What about if the table is empty to start with? So it's trying to select the MAX of a NULL value and it doesn't like it. Can you set the ID to a value if the selected records contain NULL?

I do not think that would work because the max date is determined by the data already in the table. You could insert today's date using GETDATE(). It would not make since to add the max date in the same procedure.



You could query to find the max date for either all records in the table or for an individual record (obviously after the insert).



It is generally frowned upon to have a calculated value in a table, so I would use a query or report to get that information.