Parameterised Input stored procedure
am 07.11.2007 12:36:03 von Dave dcartford
I need to create a stored procedure with a parameterised input. An input
parameter is declared as a variable,this will hold the units that a user
wants to insert from TableA into TableB. Using a CASE clause, within a
stored procedure, it will first need to compare the input parameter
against the avaiable units in TableA, let's say
in this case, an input of 3 units of Item_No (1235), is required to be
inserted into TableB's 4 columns (refer below for field names). The
stored procedure should be able to assign the units required base on the
earliest Item_ID (1st Primary key comes first, on a first-in-first-out
basis) from TableA, by first filtering a number of sequential criteria.
First it would search for the Item_No (1235) in TableA, next its
available units,if both condition are met, next, insert a row of record
into TableB's 4 columns, If units required is insufficient,
it would loop for the next available units, then inserting the next row
of records into TableB. An update procedure
is also required to be created to update the units being used for the
assignment in TableA (refer table below
for updated records for Item_No (1235).
The following are the fieldnames in TableA running on SQL Server 2000:
Dates as datetime
Item_ID as int (Primary key)
Item_No as int
Qty as int
Unit_Price as int
TableA consist of the following info :
====================================================
Dates | Item_ID | Item_No | Qty | Unit_Price
====================================================
30/10/2007 | IT1000 | 1234 | 2 | 4
====================================================
30/10/2007 | IT1001 | 1235 | 2 | 6
====================================================
28/09/2007 | IT1002 | 1236 | 4 | 8
====================================================
01/11/2007 | IT1003 | 1235 | 2 | 2
INSERT Result in TableB after executing the stored procedure :
====================================================
Dates | Item_ID | Item_No | Qty | Unit_Price
====================================================
30/10/2007 | IT1001 | 1235 | 2 | 6
====================================================
01/11/2007 | IT1003 | 1235 | 1 | 2
UPDATE Result in TableA after executing the stored procedure :
=====================================================
Dates | Item_ID | Item_No | Qty | Unit_Price
=====================================================
30/10/2007 | IT1000 | 1234 | 2 | 4
=====================================================
30/10/2007 | IT1001 | 1235 | 0 | 6
=====================================================
28/09/2007 | IT1002 | 1236 | 4 | 8
=====================================================
01/11/2007 | IT1003 | 1235 | 1 | 2
The following stored procedure was created:
Create procedure dbo.up_parmins_item
@Date_DT VARCHAR(22),
@Item_No INT,
@Qty_IT INT,
@Unit_Price_IT INT as
Declare @Item_ID INT
Select @Item_ID = Item_ID
From TableA
Where Item_No = @Item_No and Qty <> 0
-- If item not exist, then insert into TableA
If @Item_ID IS NULL
BEGIN
Insert into TableA
( Dates,Item_No,Qty,Unit_Price)
Values(Getdate(),@Item_No,@Qty_IT,@Unit_Price_IT)
Set @Item_ID = @@IDENTITY
END
-- If item exist, and Qty is not 0, then insert into TableB and Update
TableA
If @Item_ID IS NOT NULL and Qty <> 0
BEGIN
Set @Item_ID = Min(Item_ID),
Insert into TableB
( Dates ,Item_ID,Qty,Unit_Price)
Values(Getdate(),@Item_ID,@Qty_IT,@Unit_Price_IT)
-- update quantity balance for Item_No (1235) in TableA
Update TableA
Set Qty = (@Qty_IT - Qty)
Where Item_No = @Item_No
END
If @@Error > 0
BEGIN
RAISERROR ('Update and Insert Items failed',16,1)
RETURN 99
END
RETURN 0
My issue here is, how do I create a CASE clause or if-else statement to
verify the quantity (Qty) available in
TableA is sufficient for the input parameter (@Qty_IT) to be inserted
into TableB? The procedure should be able to perform a loop in the CASE
or If-else statement to allocate the quantity requested base on the
earliest
Item_ID existing in TableA, and the next available units in the
subsequent Item_ID (e.g. (1st) IT1001 - 2 units,
(2nd) IT1003 - balance of 1 unit) If the units requested (input
parameter) for an item, does not exist in TableA,
a new record is then inserted into TableA for this new item. The update
statement should be able to update the
quantity for Item_No (1235) to 0 unit under Item_ID IT1001, and 1 unit
under Item_ID IT1003.
Any suggestion to the above stored procedure would be helpful, Thanks
guys!
*** Sent via Developersdex http://www.developersdex.com ***
Re: Parameterised Input stored procedure
am 07.11.2007 14:58:19 von Dan Guzman
If the units requested (input
> parameter) for an item, does not exist in TableA,
> a new record is then inserted into TableA for this new item. The update
> statement should be able to update the
> quantity for Item_No (1235) to 0 unit under Item_ID IT1001, and 1 unit
> under Item_ID IT1003.
I didn't have time to analyze the insert requirement. Below is an example
of an alternative approach you might consider.
CREATE PROCEDURE dbo.up_parmins_item
@Date_DT VARCHAR(22),
@Item_No INT,
@Qty_IT INT,
@Unit_Price_IT INT
AS
SET NOCOUNT, XACT_ABORT ON
DECLARE
@Error int,
@Expected_Rowcount int
SELECT
@Error = 0
DECLARE @TableB TABLE
(
Dates datetime,
Item_ID char(6) NOT NULL,
Item_No int NOT NULL,
Original_Qty int NOT NULL,
Qty int NOT NULL,
Unit_Price money NOT NULL
PRIMARY KEY (Item_ID, Dates)
)
INSERT INTO @TableB
SELECT
TableA.Dates,
TableA.Item_ID,
TableA.Item_No,
Qty AS Original_Qty,
CASE
WHEN previous_inventory.begin_inventory + Qty <= @Qty_IT THEN Qty
ELSE previous_inventory.begin_inventory + Qty - @Qty_IT
END AS Qty,
TableA.Unit_Price
FROM dbo.TableA
JOIN (SELECT
Dates,
Item_No,
COALESCE((SELECT SUM(Qty)
FROM dbo.TableA AS available_stock
WHERE
available_stock.Item_No = TableA.Item_No
AND available_stock.Dates < TableA.Dates
), 0) AS begin_inventory
FROM dbo.TableA
WHERE
Item_No = @Item_No
) AS previous_inventory ON
TableA.Item_No = previous_inventory.Item_No
AND TableA.Dates = previous_inventory.Dates
WHERE
previous_inventory.begin_inventory < @Qty_IT
SELECT
@Expected_Rowcount = @@ROWCOUNT
BEGIN TRAN
UPDATE a
SET
Qty = b.Qty
FROM dbo.TableA AS a
JOIN @TableB AS b ON
b.Item_ID = a.Item_ID AND
b.Original_Qty = a.Qty
IF @@ROWCOUNT <> @Expected_Rowcount
BEGIN
RAISERROR('Inventory changed by another user. No changes were made.', 16,
1)
SET @error = 1
GOTO Done
END
INSERT INTO dbo.TableB
SELECT
Dates,
Item_ID,
Item_No,
Qty,
Unit_Price
FROM @TableB
COMMIT TRAN
Done:
IF @error <> 0
BEGIN
ROLLBACK
END
RETURN @@ERROR
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dave dcartford" wrote in message
news:4731a323$0$10303$815e3792@news.qwest.net...
>
> I need to create a stored procedure with a parameterised input. An input
> parameter is declared as a variable,this will hold the units that a user
> wants to insert from TableA into TableB. Using a CASE clause, within a
> stored procedure, it will first need to compare the input parameter
> against the avaiable units in TableA, let's say
> in this case, an input of 3 units of Item_No (1235), is required to be
> inserted into TableB's 4 columns (refer below for field names). The
> stored procedure should be able to assign the units required base on the
> earliest Item_ID (1st Primary key comes first, on a first-in-first-out
> basis) from TableA, by first filtering a number of sequential criteria.
> First it would search for the Item_No (1235) in TableA, next its
> available units,if both condition are met, next, insert a row of record
> into TableB's 4 columns, If units required is insufficient,
> it would loop for the next available units, then inserting the next row
> of records into TableB. An update procedure
> is also required to be created to update the units being used for the
> assignment in TableA (refer table below
> for updated records for Item_No (1235).
>
> The following are the fieldnames in TableA running on SQL Server 2000:
>
> Dates as datetime
> Item_ID as int (Primary key)
> Item_No as int
> Qty as int
> Unit_Price as int
>
> TableA consist of the following info :
> ====================================================
> Dates | Item_ID | Item_No | Qty | Unit_Price
> ====================================================
> 30/10/2007 | IT1000 | 1234 | 2 | 4
> ====================================================
> 30/10/2007 | IT1001 | 1235 | 2 | 6
> ====================================================
> 28/09/2007 | IT1002 | 1236 | 4 | 8
> ====================================================
> 01/11/2007 | IT1003 | 1235 | 2 | 2
>
> INSERT Result in TableB after executing the stored procedure :
> ====================================================
> Dates | Item_ID | Item_No | Qty | Unit_Price
> ====================================================
> 30/10/2007 | IT1001 | 1235 | 2 | 6
> ====================================================
> 01/11/2007 | IT1003 | 1235 | 1 | 2
>
> UPDATE Result in TableA after executing the stored procedure :
> =====================================================
> Dates | Item_ID | Item_No | Qty | Unit_Price
> =====================================================
> 30/10/2007 | IT1000 | 1234 | 2 | 4
> =====================================================
> 30/10/2007 | IT1001 | 1235 | 0 | 6
> =====================================================
> 28/09/2007 | IT1002 | 1236 | 4 | 8
> =====================================================
> 01/11/2007 | IT1003 | 1235 | 1 | 2
>
> The following stored procedure was created:
>
> Create procedure dbo.up_parmins_item
> @Date_DT VARCHAR(22),
> @Item_No INT,
> @Qty_IT INT,
> @Unit_Price_IT INT as
>
> Declare @Item_ID INT
>
> Select @Item_ID = Item_ID
> From TableA
> Where Item_No = @Item_No and Qty <> 0
> -- If item not exist, then insert into TableA
> If @Item_ID IS NULL
> BEGIN
> Insert into TableA
> ( Dates,Item_No,Qty,Unit_Price)
> Values(Getdate(),@Item_No,@Qty_IT,@Unit_Price_IT)
> Set @Item_ID = @@IDENTITY
> END
> -- If item exist, and Qty is not 0, then insert into TableB and Update
> TableA
> If @Item_ID IS NOT NULL and Qty <> 0
> BEGIN
> Set @Item_ID = Min(Item_ID),
> Insert into TableB
> ( Dates ,Item_ID,Qty,Unit_Price)
> Values(Getdate(),@Item_ID,@Qty_IT,@Unit_Price_IT)
> -- update quantity balance for Item_No (1235) in TableA
> Update TableA
> Set Qty = (@Qty_IT - Qty)
> Where Item_No = @Item_No
> END
>
> If @@Error > 0
> BEGIN
> RAISERROR ('Update and Insert Items failed',16,1)
> RETURN 99
> END
>
> RETURN 0
>
> My issue here is, how do I create a CASE clause or if-else statement to
> verify the quantity (Qty) available in
> TableA is sufficient for the input parameter (@Qty_IT) to be inserted
> into TableB? The procedure should be able to perform a loop in the CASE
> or If-else statement to allocate the quantity requested base on the
> earliest
> Item_ID existing in TableA, and the next available units in the
> subsequent Item_ID (e.g. (1st) IT1001 - 2 units,
> (2nd) IT1003 - balance of 1 unit) If the units requested (input
> parameter) for an item, does not exist in TableA,
> a new record is then inserted into TableA for this new item. The update
> statement should be able to update the
> quantity for Item_No (1235) to 0 unit under Item_ID IT1001, and 1 unit
> under Item_ID IT1003.
>
> Any suggestion to the above stored procedure would be helpful, Thanks
> guys!
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***