My First Stored Proc

The Mighty Dubs

Hi guys,

Im attempting to create an update stored procedure. Probably not the wisest thing considering its my first ever stored proc. So here she goes. basically i have a table with holds 1000's of items. Each row contains the following: itemcode,
item description,price,qty. if there are e.g. 5 qtys for one item they will take up 5 lines with the same itemcode. When a brand new item appears the itemcode will increment by one. I have created an asp form that allows only one item at a time but up to 5 qty's be uploaded. I have created another table which I have called tblItemCodeCounter with has 3 fields called CounterID, TableRef, ItemCode.

My questions are;

1)Will the following SP update the table anytime a new item is inserted into the table without an itemcode.
2) If not, how do i get it to do so.
3) Do I need to add anything to the insert statement to trigger the update.

/*

Name: ins_itemCode
Description: This will insert a unique incremental itemcode to every new item inserted into the TblUnitmatrix from the rapidQuote form.
*/
CREATE PROCEDURE [dbo].[ins_itemCode]
(
@CounterID INT OUTPUT
)
AS
SELECT @CounterID = ItemCode from tblItemCodeCounter

UPDATE TBLITEMCODECOUNTER
SET ItemCode=ItemCode+1
WHERE COUNTERID=1

RETURN @CounterID
GO

John_Mc

The Mighty Dubs wrote: »

Hi guys,

Im attempting to create an update stored procedure. Probably not the wisest thing considering its my first ever stored proc. So here she goes. basically i have a table with holds 1000's of items. Each row contains the following: itemcode,
item description,price,qty. if there are e.g. 5 qtys for one item they will take up 5 lines with the same itemcode. When a brand new item appears the itemcode will increment by one. I have created an asp form that allows only one item at a time but up to 5 qty's be uploaded. I have created another table which I have called tblItemCodeCounter with has 3 fields called CounterID, TableRef, ItemCode.

My questions are;

1)Will the following SP update the table anytime a new item is inserted into the table without an itemcode.
2) If not, how do i get it to do so.
3) Do I need to add anything to the insert statement to trigger the update.

/*

Name: ins_itemCode
Description: This will insert a unique incremental itemcode to every new item inserted into the TblUnitmatrix from the rapidQuote form.
*/
CREATE PROCEDURE [dbo].[ins_itemCode]
(
@CounterID INT OUTPUT
)
AS
SELECT @CounterID = ItemCode from tblItemCodeCounter

UPDATE TBLITEMCODECOUNTER
SET ItemCode=ItemCode+1
WHERE COUNTERID=1

RETURN @CounterID
GO

Isnt a Trigger exactly what this type of thing is for?

Read up on it here

The Mighty Dubs

You'll have to forgive John Mc, im only fairly new to this SQL Server procs & triggers and the like. Cheers for the link, will have a go at it.

John_Mc

The Mighty Dubs wrote: »

You'll have to forgive John Mc, im only fairly new to this SQL Server procs & triggers and the like. Cheers for the link, will have a go at it.

Ah no worries man, never used Triggers myself - I just knew that's what they were for. Hope it helps somewhat anyway!

I'd say there isnt much difference between them and SP's either

amen

triggers may have performance cost depending on what you are doing and the index type on your table

but reading your initial post and am I correct in the following:
itemcode,item description,price,qty
1,test,23,4
2,test2,45,5
1,test,23,6

i.e you have itemcode 1 in the table twice with different qty?
if this is not the best idea

Delirium

Just wondering why you don't have a table which would contain the Itemcode and ItemDescription? That way you would have a sample table listing all items. You would use the counter in tandem with the item table to get the ItemCode and then pass it down to the table that would contain the 1000s of records.

Maybe its not appropriate for what your doing. It just sounds like it should be 3 tables to me, tblCounter, tblItemCode and tblItemTracker for example.

pauldiv

The word "GO" at the end of the SP fires the update.
A trigger is a mechanism for performing an action on another table after you have executed your SP.

The Mighty Dubs

Thanks all for your input,
I actually didnt run with the trigger or proc in the end, i just coded it into the asp and it does the job nicely. Here you go if anyone is interested...

sSQL = "SELECT CounterValue FROM tblRQCounter"
VIEWRECORDSET RS,SSQL
sItemCode=rs.fields("CounterValue").value

sSQL = "UPDATE tblRQCounter SET CounterValue=CounterValue+1"
ExecuteCommand sSQL.