asked on

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

CarlVerret

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

nkewney

ASKER

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

Vitor Montalvão

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

nkewney

ASKER

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

Vitor Montalvão

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

nkewney

ASKER

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

Vitor Montalvão

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

nkewney

ASKER

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

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nkewney

ASKER

Sorry it took so long!