April 10, 2009 at 12:50 pm
Hello,
I have a simple Insert SP.
ALTER PROCEDURE [dbo].[usp_InsItem]
@Part_Num varchar(50)=NULL
AS
BEGIN
SET NOCOUNT OFF;
INSERT INTO Items
( [PartNum], [ItemOrder] )
VALUES( @Part_Num)
And have a simple two column table ( PartNum, ItemOrder)
Now in my SP I want to Calculate the Highest ItemOrder which the PartNum has in the table and then insert that to the new row.
So basically lets say I have two entries of a PartNum - 12345, so when I insert another PartNum in the table I want to Insert ItemOrder 3. How can I calculate the Highest ItemOrder for that PartNum and then insert the next one?
Thanks
April 10, 2009 at 1:16 pm
You can write the insert statement like
INSERT INTO myTable1([Col1], [Col2])
SELECT [Col1], [Col2]
FROM myTable2
WHERE [Col1] IN (SELECT MAX([Col1] FROM myTable2)
Does this solves your need.
April 10, 2009 at 1:37 pm
You could also try...
INSERT INTO Items ([PartNum], [ItemOrder])
SELECT @Part_Num, MAX(ItemOrder)
FROM TableX
WHERE TableX.Part_Num = @Part_Num
April 10, 2009 at 1:53 pm
ALTER PROCEDURE [dbo].[usp_InsItem]
@Part_Num varchar(50)=NULL
AS
BEGIN
SET NOCOUNT OFF;
INSERT INTO Items
( [PartNum], [ItemOrder] )
VALUES( @Part_Num,
,(SELECT MAX(ItemOrder)
FROM Items
WHERE Part_Num =@Part_Num)
)
I am getting the Error if I do the above:
Subqueries are not allowed in this context. Only scalar expressions are allowed.
April 10, 2009 at 1:57 pm
Don't make it a subquery. There is no need. Just select the max value for the colum in a standard select and insert the variable value for the other column.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy