[RESOLVED] SQL 2005 - Stored Procedures

[Greyskull]

Hi all,

Thought I'd try creating my first stored procedure for SQL, but don't know if what I'm thinking is posisble! so pls bear with me,

Say I've got 2 tables - tblCards and tblBatch.

tblBatch has BatchNo,DateDelivered,BatchQty,QtyUsed
tblCards has CardId, CardSerial, BatchNo, Issued, DateUsed

Say if a Card in tblCards has been updated to say Issued='Y' and DateUsed=whatever the date is, is it possible to automatically update tblBatch.QtyUsed where the card is associated with?

Hope this makes sense.

Any help is much appreciated

Greyskull

[wild_bill]

Something like this?

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE IncrementQuantityUsed 
	-- Add the parameters for the stored procedure here
	@dateUsed datetime
AS
BEGIN

	UPDATE b
	set QtyUsed = QtyUsed + 1
	from tblBatch b
	inner join tblCards c on b.BatchNo = c.BatchNo
	where c.Issued = 'Y' and c.DateUsed = @dateUsed
	
END
GO

[Greyskull]

Hi,

Thanks for this, I have successfully created the stored procedure. Do I need to call it when I do updates or would it automatically run if the sims are updated?

Thanks again

[Greyskull]

I think I figured this out, I've created a stored procedure using the following code and found a way to call the stored procedure, then voila :-)

USE SAMPLE_Cards
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Greyskull
-- Create date: 2012-03-07
-- Description:	Automatically update SAMPLE_tblBatch of how many Cards are used
-- =============================================
CREATE PROCEDURE IncrementQtyUsed_SAMPLEtblCards
	-- Add the parameters for the stored procedure here
	@batchNo int
AS
BEGIN
	
	DECLARE @QtyLeft int
	DECLARE @OrigQty int
	
	SELECT @QtyLeft=COUNT(*) FROM SAMPLE_tblCards WHERE 
		BatchNo=@batchNo and Issued IS NULL
	
	SELECT @OrigQty=BatchQty FROM SAMPLE_tblBatch WHERE
		BatchNo=@batchNo
		
    UPDATE b
    SET QtyUsed = @OrigQty - @QtyLeft
    FROM SAMPLE_tblBatch b
    INNER JOIN SAMPLE_tblCards s on b.BatchNo = s.BatchNo
    WHERE b.BatchNo=@batchNo
END
GO

Do you think this is ok?

Thanks

[FunkyDexter]

Have you considered using a trigger for this?

From your description it sounds like you want this to happen every time you update the Issued field on any card record. Is that right? If so, a trigger could be your freind. You set up an Update trigger on your Card table and tell it to run your sproc. That way you won't have to call your sproc from code when you update the card table. This is a good thing because it means you'll never forget to put that call in your code, leading to data corruption.

There is a downside though. Many developers (myself included) get very nervous around triggers. The reason is that it's easy to forget about a trigger. Sometimes we run an update against the table and forget the trigger is there. All of a sudden the trigger starts firing and updating stuff we weren't expecting it to. So te rule of thumb is, put it in a trigger if you realy, REALLY want the update to happen EVERY time.

[Greyskull]

Ahh, I really do need it to be there. Sounds just like what I need.

Could you point me to the right direction as to how to do it and perhaps a sample

Thanks

[FunkyDexter]

They're probably in the tutorials in this forum. Hang on, I'll check.




hmm, nothing in the tutorials. It's pretty simple though:-
1. In management studio, select the table you want to add the trigger to and click the plus so you can see it's children. One of them is called triggers.
2. Right click on the triggers node and select "New Trigger"
3. It'll create the basic sql for you to create a trigger in a new query window.
4. Give your trigger a name by replacing this bit of code: <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name>
5. Replace this bit of code with the name of your table: <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name>
6. Replace this bit of code: <Data_Modification_Statements, , INSERT,DELETE,UPDATE> with either INSERT, UPDATE or DELETE, depending on when you want your trigger to fire. You can have more than one option if you want it to fire in more than one situation. So this: AFTER INSERT,UPDATE would create a trigger that fire whenever an insert or update is done against the table
7. Put a call to your sproc in the main "code bit", just after where it says Set NoCount On.

You'll probably need to know which card was updated so you can work out which batch to update. There are two system tables called INSERTED and DELETED that contain any records that were affected by the operation which fired the trigger. For an Update operation the Deleted table contains the row as it was before the update and Inserted contains the row as it was after the update.

That should be all you need. Have a go and post back if you run into any difficulties.

[Greyskull]

Thanks a bunch, I shall wait

[FunkyDexter]

Hah! Looks like our posts crossed over. Look up.

[Greyskull]

Oh! haha

Sweet, I'll first have a play on a test db I shall let you know, how I get on.


All is working good now

Thanks a lot for all the help and pointing to the right direction