Stored procedure problem, I think.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Stored procedure problem, I think.

Author

Topic

owen912
Starting Member

31 Posts

Posted - 2003-04-10 : 14:31:45

I am trying to troubleshoot a stored procedure problem and I am hoping someone here can help. In my stored procedure, I am inserting data into a parent orders table, get the new order ID and then insert data into the child order details table.

What happens, and this does not happen all the time, is the child table fails to get inserted.

Below is an example of the procedure, edited for brevity:

BEGIN

INSERT dbo.orders (personID, orderDate, total) VALUES (@personID, @orderDate, @total)

@OrderID = @@IDENTITY

END

BEGIN

INSERT dbo.tblOrderDetail (orderID, productID, unitPrice, quantity)

SELECT @orderID AS orderID, productID, unitPrice, quantity
FROM dbo.tblShoppingCart
WHERE (shopperID = @shopperID)

END

The above is contained in a single stored procedure, and most of the time it works fine. The frustrating part is has never failed for my, only others.

Thanks in advance for any insight.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-10 : 14:38:38

Well what is the failure, meaning what error do they get? It could be a problem with @@IDENTITY. You might consider using SCOPE_IDENTITY instead. Look up both to determine the differences. Since you only gave partial code, I can't recommend which one to use. Other than that though, the code looks fine, so just need to know what error do other people get when they run it.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-10 : 14:44:34

I don't know...seems kind of dicey to me...@@IDENTITY Stores the last identity value generated...doesn't have to have to do with anything you have here... in the split second (or less) that your first insert occurs and the time of your set @variable, the identity could be different (I think). Couldn't you join orders to shoppingcart to get the order Id, joining on personId where orderdate = @OrderDate and Total = @Total?

Just a thought.

One other note, I would seriously put in Error Checking, esp since your dealing with money (I presume).

I posted some error handling routine in this thread:

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=25272

Good Luck

Brett

8-)

Edited by - x002548 on 04/10/2003 14:50:10

owen912
Starting Member

31 Posts

Posted - 2003-04-10 : 14:52:44

I have not been able to determine the error at this point. I have error handling in the ASP code that sends me an email with details.

The omitted code is more tables inserting customer info, and more columns in the order table. When this problem happens, data is entered in the parent table and not the child table.

I will research SCOPE_IDENTITY.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-10 : 14:53:25

quote:

@OrderID = @@IDENTITY

This shouldn't compile ... I think you need a SET or SELECT in front of it ...

Jay White
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-10 : 14:55:52

SCOPE_IDENTITY is the way to go usually, at least that's what I have heard.

Tara

owen912
Starting Member

31 Posts

Posted - 2003-04-10 : 14:58:38

>>>@OrderID = @@IDENTITY

Sorry for the confusion. I do have SET @OrderID = @@IDENTITY in the actual for. Bad typing on my part.

Mike

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-10 : 17:10:12

SCOPE_IDENTITY....very nice. Thanks Tara. Never even knew about this....only knew of @@IDENTITY and made it my religon not to use it (any good examples of why you would?)

BOL:

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Syntax
SCOPE_IDENTITY( )

Return Types
sql_variant

Remarks
SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions in that they return values inserted into IDENTITY columns.

IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.

SCOPE_IDENTITY and @@IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.

Assuming that both T1 and T2 have IDENTITY columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.

@@IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.

SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources