• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

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

0
nkewney
Asked:
nkewney
  • 5
  • 4
1 Solution
 
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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
nkewneyAuthor Commented:
Sorry it took so long!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now