Stored Procedure - Checking if a record exists..

Dear Experts,

I'm having a problem with an SQL stored procedure (shown below)

I'm taking in a dataset and adding the rows to table 'OrderItems'.

However, OrderItems refers to column 'Item' in table Items.

So, if an Item doesn't exist... i want to add it to Items first, get the ItemID and add it to OrderItems.

If it does exist, I want to get the ID and add it with it's existing ID?

Could anybody give me some pointers?

OrderItems has columns
OrderItemID, OrderID, ItemID, OrderItemQty

Items has columns
ItemID ... etc.

Thanks in advance to anybody who can lend a hand!

Nick
CREATE PROCEDURE [dbo].[CreateOrder] 
/** Company **/
 
@Data xml,
@Company_ID int,
@Username varchar(50)
 
AS
 
/** Make a unique identifier for order **/
 
declare @OrderID1 int 
EXEC @OrderID1 = AddOrder @Company_ID, @Username 
 
INSERT INTO OrderItems (OrderID,OrderItemQty)
 
SELECT
 
	 @OrderID1 AS OrderID,
	x.d.value('Diff[1]','INT') AS OrderItemQty
 
FROM @Data.nodes('/NewDataSet/OrderItems')  x(d)

Open in new window

LVL 1
nkewneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

CarlVerretCommented:
Is your problem related with retreiving the IDENTITY value retruned when the INSERT is done in the Items table ? If so, you could just look at the @@IDENTITY global variable that returns the last number that was generated by SQL
0
nkewneyAuthor Commented:
Hi there,

No - this isn't the problem

I'm doing an INSERT SELECT (below) but want to check each record first before they're inserted (to make sure it's not already in Items)

Nick
INSERT INTO OrderItems (OrderID,OrderItemQty)
 
SELECT
 
	 @OrderID1 AS OrderID,
	x.d.value('Diff[1]','INT') AS OrderItemQty
 
FROM @Data.nodes('/NewDataSet/OrderItems')  x(d)

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can add more SQL to your statement:
INSERT INTO OrderItems (OrderID,OrderItemQty)
SELECT
	@OrderID1 AS OrderID,
	x.d.value('Diff[1]','INT') AS OrderItemQty
FROM @Data.nodes('/NewDataSet/OrderItems')  x(d)
WHERE @OrderID1 NOT IN (SELECT OrderID FROM OrderItems)

Open in new window

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

nkewneyAuthor Commented:
Great - getting closer!

If they do appear in 'Items', I want to add them to this table first, if not I want to get the itemID from Items and add them to OrderItems.

Does that make sense?

Thanks

Nick
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just add another INSERT statement after this one. Should be something like (you might to work more 2nd INSERT because I don't know the fields that you really want):
INSERT INTO OrderItems (OrderID,OrderItemQty)
SELECT
	@OrderID1 AS OrderID,
	x.d.value('Diff[1]','INT') AS OrderItemQty
FROM @Data.nodes('/NewDataSet/OrderItems')  x(d)
WHERE @OrderID1 NOT IN (SELECT OrderID FROM OrderItems)
 
 
INSERT INTO OrderItems (OrderID,OrderItemQty)
SELECT
	@OrderID1 AS OrderID,
	x.d.value('Diff[1]','INT') AS OrderItemQty
FROM @Data.nodes('/NewDataSet/OrderItems')  x(d)
WHERE @OrderID1 IN (SELECT OrderID FROM OrderItems)

Open in new window

0
nkewneyAuthor Commented:
Ok I think I understand. Just to clarify...

I have two tables

-OrderItems
-Items

I'm trying to dump a dataset into OrderItems, but first I want to check whether the CatNumber exists in Items.CatNumber. If it does, I want to get the ID of that record and write it to OrderItems with the rest of the data.

I'm confusing myself now...

Does this make sense to you?

Nick

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Isn't confusing...
...yet! :)

You can do i t with a single INSERT statement:
INSERT INTO OrderItems (OrderID, field1, field2, ..., fieldN)
SELECT Items.OrderID, field1, field2, ..., fieldN
FROM Items
WHERE Items.CatNumber = @CatNumber

Open in new window

0
nkewneyAuthor Commented:
Great VMontalvao,

But what if  ...

"SELECT Items.OrderID, field1, field2, ..., fieldN
FROM Items
WHERE Items.CatNumber = @CatNumber"

doesn't exist...

Then I need to:

Add the details to Items
Get the new ID
Insert into OrderItems.

Nick
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just add an INSERT statement before that one. Like that when you reach 2nd INSERT the records will be there already.
Should me something like this (I don't know but maybe you need to put 1st INSERT inside a LOOP):
INSERT INTO Items (OrderID, CatNumber, ..., fieldN)
VALUES @OrderID, @CatNumber, ..., fieldN
GO
 
INSERT INTO OrderItems (OrderID, field1, field2, ..., fieldN)
SELECT Items.OrderID, field1, field2, ..., fieldN
FROM Items
WHERE Items.CatNumber = @CatNumber

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nkewneyAuthor Commented:
Sorry it took so long!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.