[RESOLVED] SQL 2005 - Stored Procedures

[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]

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.