Link to home
Start Free TrialLog in
Avatar of HarleySkater
HarleySkaterFlag for United States of America

asked on

Stored Procedure, Insert with subquery

I want to create a stored procedure that uses a subquery to insert the max Identity from a table into another table (last record added)  
I wrote the code but it says that sub queries are not allowed in the insert statements, only scalar expressions.  If its not possible, how do I use the same select statement to save the return to a scalar value?
CREATE PROCEDURE NewCaseInsertDocs 
	-- Add the parameters for the stored procedure here
	@MaxCaseId int
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
    -- Insert statements for procedure here
  	INSERT INTO dbo.Documents_Needed 
	                      (CaseID, Type)
	VALUES     ((SELECT MAX(CaseID) From dbo.ACF_Cases),'Assign')
END
GO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of chapmandew
chapmandew
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
as chap has shown, you don't use VALUES in conjunction with a SELECT.  

no points please.
Avatar of HarleySkater

ASKER

Im awarding all the points to Brandon :P J/k
 
Thanks works like a charm.
Is there a way to set the scalar value? to the select statement.  I know its 2 statements instead of 1 but its still good information to know
 
Thank yoU!
superman fast :P