Problem with a SP. Help!

Why does only the first INSERT statement work?

CREATE PROCEDURE insert_inventory
@.item_name varchar(20),
@.description varchar(100),
@.notes varchar(255),
@.amount varchar(8)
AS
DECLARE @.item_id int
DECLARE @.transaction_date datetime

IF (@.item_name = '') SET @.item_name = NULL
IF (@.description = '') SET @.description = NULL
IF (@.notes = '') SET @.notes = NULL
IF (@.amount = '') SET @.amount = NULL

SET @.item_id = IDENT_CURRENT('inventory')
SET @.transaction_date = GETDATE()

INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))

Also, where I have IF(@.blah = '') SET...

I use these to convert blank fields in VB6 to NULL values, is there any easier way?do you get any error on the second insert?

if @.blah='' set... can be removed, but you will have to check for the value somewhere if you care for it: either in your vb6, in the if as you do now, or in the values clause (...values (nullif(@.blah, ''), ...)|||Try to change your SP this way (you have to get id after insert - not before):

SET @.transaction_date = GETDATE()
begin tran
INSERT INTO inventory (item_name, item_description, notes) VALUES (@.item_name, @.description, @.notes)
if @.@.error<>0 begin
rollback
return
end
SET @.item_id = IDENT_CURRENT('inventory')

INSERT INTO expenditure (item_id, transaction_date, amount) VALUES (@.item_id, @.transaction_date, CAST(@.amount AS money))
commit|||i think ident_current(..) returns the _last_ new identity value for the specified table, not the identity value generated by the current scope. in this case if your insert occurred before another insert in another scope you may acquire the value that is not yours. scope_identity() guarantees that identity value belongs to insert from your session.

also, it's better to write the error handler this way:

declare @.error int, @.id int
...insert operation
select @.error = @.@.error, @.id = scope_identity()
if @.error <> 0 begin
raiserror (...)
rollback tran
return (1)
end
commit tran|||Cheers, for those, they were all good recommendations which I am now using, but it still would only execute the first INSERT statement. That is until I used this:

SET NOCOUNT ON

Works beautifully now!!!

Rayden