Stored Procedure Problems

They have: 82 posts

Joined: Oct 2001

I have a SQL Script that works. However, when I make it a stored procedure it does nothing.

Here is the working script:

Declare @Balance money /* BALANCE ON CARD*/
Declare @Fee money /* FEE DEPENDS ON TYPE OF TRANSACTION*/
Declare @CustomerNumber int /* CUSTOMER NUMBER THAT CORRESPONDS WITH CARD*/
Declare @TransAmount MONEY /* THE AMOUNT OF THE TRANSACTION IF WITHDRAWAL*/

SELECT DISTINCT
@TransAmount=100,

@Balance=Card.CustomerBalance,
@CustomerNumber=Card.CustomerNumber
From dbo.Card Card
Where Card.CardNumber=1234 /*@CardNumber*/

Select
@Fee=SC.ATMWithdrawalFee
From dbo.ServiceCharge SC
Where SC.CustomerNumber=@CustomerNumber

If @Balance > @TransAmount + @Fee /*@transamount*/ /* MAKE SURE THAT THEY HAVE*/
/* ENOUGH MONEY INCLUDING FEE*/
Update Card
Set CustomerBalance=@Balance - @TransAmount - @Fee
Where Card.CardNumber=1234

-------------------------------------------------------------------

Here is what I have for a stored proc:
It does nothing but return Nulls. The one above updates 2 rows.
Any ideas?

Thanks
- Mike

CREATE PROCEDURE ap_Test
@CardNumber int,
@type int=Null,
@TransAmount money=Null
AS
BEGIN

Declare @Balance money /* BALANCE ON CARD*/
Declare @Fee money /* FEE DEPENDS ON TYPE OF TRANSACTION*/
Declare @CustomerNumber int /* CUSTOMER NUMBER THAT CORRESPONDS WITH CARD*/

Declare @TransType Int /* GET THE TRANSACTION TYPE*/
SELECT @CustomerNumber AS CustomerNumber,
@cardnumber as CardNumber,
@Balance as Balance
SELECT DISTINCT

@Balance=Card.CustomerBalance,
@CustomerNumber=Card.CustomerNumber,
@Fee=SC.ATMWithdrawalFee
From dbo.Card Card,
dbo.ServiceCharge SC
Where Card.CardNumber=@CardNumber
and SC.CustomerNumber=@CustomerNumber

If @Balance > @TransAmount + @Fee /* MAKE SURE THAT THEY HAVE*/
/* ENOUGH MONEY INCLUDING FEE*/
Update Card
Set CustomerBalance=@Balance - @TransAmount - @Fee
Where Card.CardNumber=@CardNumber
End

Blessed is the man who fears the LORD, who delights greatly in his commandments. Psalms 112:1

Peter J. Boettcher's picture

They have: 812 posts

Joined: Feb 2000

Did you try hard coding the card/customer numbers? The structure/syntax looks ok to me. You might want to consider putting brackets around (@TransAmount + @Fee) just to make it easier to follow the logic.

PJ | Are we there yet?
pjboettcher.com

Want to join the discussion? Create an account or log in if you already have one. Joining is fast, free and painless! We’ll even whisk you back here when you’ve finished.