Cloning a row or Execute Sproc within Sproc

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

General SQL Server Forums

New to SQL Server Programming

Cloning a row or Execute Sproc within Sproc

Author

Topic

ScottBot
Starting Member

16 Posts

Posted - 2011-04-11 : 13:24:55

I have what is looking like an awkward situation and am wondering if anyone may have a better option / solution.

I have created an allocation table that holds the following data

item_ID, allocated_amount, allocation_status

If I assign an item I get the following values in my table

1 , 50 , In Store

I can update the table easily enough if items are sold, however if I now have to send 20 t-shirts to another store I need to keep a record of the amount I sent out as well as an updated amount of the t-shirts I now have in store.

I considered cloning the row and updating the clone with the new stock level but I am unable to auto increment a cloned row (would it be an idea / possible to add a second unique id row?

so I would get item_ID, product_ID, allocated_amount, allocation_status, which would then allow me to clone each item based on the product_ID and auto increment the item_ID that way.

I have also tried to get a stored procedure on update to Archive the t-shirts being sent out and execute the insert sproc to insert a new allocation based on the original amount and subtract the sent out items. I could,t seem to get this working either and I imagine this is due to my limited knowledge of SQL.

Any one got some examples or thoughts on this.

Appreciate any help I can get.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-04-11 : 19:13:46

It's not clear to me what you are trying to accomplish. If you have 50 t-shirts in-house and you ship 20 to another store, why can't you simply change the in-store quantity to 30 and perhaps have another entry for the 20 in transit? I'm sure that "in-store" is not the only status; you must have, for example, a status of "ordered". Can you elaborate on the problem?

=======================================
Elitism is the slur directed at merit by mediocrity. -Sydney J. Harris, journalist (1917-1986)

McDebil
Starting Member

23 Posts

Posted - 2011-04-12 : 07:09:08

Try use this logic:

DECLARE @table TABLE
(
[Item_ID] INTEGER,
[allocated_amount] INTEGER,
[Allocation_ID] INTEGER
)

DECLARE @Allocations TABLE
(
[Allocation_ID] INTEGER,
[Allocation_Description] VARCHAR(100)
)

INSERT @Allocations ([Allocation_ID], [Allocation_Description])
VALUES (1 , 'Store1' )
INSERT @Allocations ([Allocation_ID], [Allocation_Description])
VALUES (2 , 'Store2' )
INSERT @Allocations ([Allocation_ID], [Allocation_Description])
VALUES (3 , 'On the road from Store1 to Store"' )
INSERT @Allocations ([Allocation_ID], [Allocation_Description])
VALUES (4 , 'On the road from Store2 to Store1' )

INSERT @table ([Item_ID], [allocated_amount], [Allocation_ID])
VALUES (111 ,50, 1)

SELECT i.[Item_ID]
, i.[allocated_amount]
, a.[Allocation_Description]
FROM @table i
INNER JOIN @Allocations a
ON [i].[Allocation_ID] = a.[Allocation_ID]

-- send 20 pieces to Store2
-- Create procedure by the following logic:
DECLARE @Amount INTEGER
DECLARE @ItemID INTEGER
DECLARE @Allocated INTEGER
DECLARE @NewLocation INTEGER

SET @Amount = 20
SET @ItemID = 111
SET @Allocated = 1
SET @NewLocation = 3

UPDATE @table SET [allocated_amount] = [allocated_amount] - @Amount
WHERE [Item_ID] = @ItemID AND [Allocation_ID] = @Allocated

IF EXISTS (SELECT 1 FROM @table WHERE [Item_ID] = @ItemID AND [Allocation_ID] = @NewLocation)
BEGIN
UPDATE @table SET [allocated_amount] = [allocated_amount] + @Amount
WHERE [Item_ID] = @ItemID AND [Allocation_ID] = @NewLocation
END
ELSE
BEGIN
INSERT @table ( [Item_ID], [allocated_amount], [Allocation_ID])
VALUES (@ItemID, @Amount, @NewLocation)
END

-- End of procedure

SELECT i.[Item_ID]
, i.[allocated_amount]
, a.[Allocation_Description]
FROM @table i
INNER JOIN @Allocations a
ON [i].[Allocation_ID] = a.[Allocation_ID]

McDebil

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources