I don't see a problem in this procedure just by looking at it. I believe the logic is written correctly, so it's very puzzling.
>yes and yes.
>
>The problem is not what's being passed to the proc, it's that batchid is always being incremented inside the proc. What's being
>passed is always the last value sent back out, where for a series of calls it should be the same.
>
>
>
>
>
>
>>Did you test this from SSMS? Did you try addint print @BatchID at the top of the procedure?
>>
>>>In this proc I pass in 0 or a BatchId from a previous run. If a batchId is passed, I want to use it, otherwise, compute the next
>>>batch Id and use that.
>>>
>>>This is alwats incrementing the batch Id. I don't see what's wrong.
>>>
>>>
>>>
>>>
>>>CREATE PROCEDURE ap_AddInventoryRecord
>>> @PartId INT = NULL,
>>> @WarehouseId INT = NULL,
>>> @LocationId INT = NULL,
>>> @UserId INT = NULL,
>>> @SerialNo VARCHAR(30) = NULL,
>>> @Quantity INT = 1,
>>> @DropShip TINYINT = NULL,
>>> @Printed TINYINT = NULL,
>>> @ActionDate DATETIME = NULL,
>>> @PurchaseOrder VARCHAR(30) = NULL,
>>> @Comments TEXT = NULL,
>>> @InventoryId INT = NULL OUTPUT,
>>> @BatchId INT OUTPUT
>>>
>>>AS
>>>BEGIN
>>>
>>> IF @ActionDate IS NULL
>>> BEGIN
>>> SET @ActionDate = GETDATE()
>>> END
>>>
>>> IF(@BatchId IS NULL OR @BatchId = 0)
>>> BEGIN
>>> SELECT @BatchId = MAX(BatchId) + 1 FROM Inventory
>>> END
>>>
>>> IF(@BatchId IS NULL)
>>> BEGIN
>>> SET @BatchId = 1
>>> END
>>>
>>> INSERT INTO Inventory
>>> (BatchId, PartId, WarehouseId, LocationId, UserId, SerialNo, Quantity,
>>> DropShip, Printed, ActionDate, PurchaseOrder, Comments)
>>> VALUES
>>> (@BatchId, @PartId, @WarehouseId, @LocationId, @UserId, @SerialNo, @Quantity,
>>> @DropShip, @Printed, @ActionDate, @PurchaseOrder, @Comments)
>>>
>>> SET @InventoryId = SCOPE_IDENTITY()
>>>
>>>END
>>>
If it's not broken, fix it until it is.
My Blog