Unanswered: Scheduling a job using a stored procedure
I currently new to SQL server and have been assigned a project to develop an auction site as part of my course. I would like to create a stored procedure which schedules a job to modify the 'auction state' field in a table to 'active' once the auction start time is reached and also run a scheduled job to close the auction at the end time.
I was thinking about using a stored procedure which calls on the sp_add_job but you have to use the msdb for this and you cannot use the 'USE' keyword inside a stored produre to call this. Am I going the wrong way about this or is it possible?
Can anyone see a problem with the code below, STEP1 doesn't get included when the job is scheduled. Suspected errors highlighted in red. The errors haven't been added cos they are confusing.
INSERT INTO Auction
@AuctionID = @@Identity
declare @Aid varchar
set @Aid = convert(varchar,@auctionID,15)
Declare @sqlcommand varchar(255)
Set @sqlcommand = 'UPDATE Auction SET AuctionState = ''closed'' WHERE AuctionID = ' + @AID