Stored Procedure not working properly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Stored Procedure not working properly

Author

Topic

ndn_24_7
Starting Member

23 Posts

Posted - 2004-12-01 : 12:32:34

I’m having a problem with updating two tables through a stored procedure. The program issues drawing vouchers to players who participate in promotions with certain departments. Each department can issue an x amount of tickets to players. The vouchers are printed through a crystal report. The issuer inputs 4 parameters. Whenever a voucher is issued to a player for the first time, vouchers come up fine. But whenever they get issued vouchers the second time for the same department, nothing comes up, even though they are qualified to receive more vouchers. For example, John Doe plays blackjack for 3 hours, which earned him 3 tickets, and he receives 3 tickets. The next day he comes back, plays 5 hours, which he should earn 5 tickets. When he goes to redeem the tickets to the players club, the voucher reports comes up blank and the tables don’t update. I can’t figure out where things are going wrong in the stored procedure. Whenever a voucher is issued, the DeptDrawingVouchers should update with a count of how many vouchers where issued to that player ID and the DepartmentVouchers table should track the every ticket that was issued. The tables involved are as follows:

DepartmentConfig
DepartmentID int 4 0
DepartmentName char 10

DepartmentVouchers
PlayerID int 4
DepartmentID int 4
UserID int 4
VoucherNumber int 4
Issued char 1
IssueDate datetime 8
(VoucherNumber is the Identity and increments by 1)

DeptDrawingVouchers
PlayerID int 4
DepartmentID int 4
VouchersRedeemed int 4
VouchersIssued int 4

The stored procedure is a follows:

CREATE PROCEDURE dbo.Department_IssueDrawingVoucher

@@PlayerID INT,
@@DepartmentID INT,
@@UserID INT,
@@VouchersRedeemed INT
AS

Declare @@IssueDate DateTime

Select @@IssueDate=getdate()

if not exists(select PlayerID from DeptDrawingVouchers
where PlayerID = @@PlayerID
and DepartmentID = @@DepartmentID)

insert into DeptDrawingVouchers
values(@@PlayerID,@@DepartmentID,0,0)

update DeptDrawingVouchers
set VouchersRedeemed = @@VouchersRedeemed
where PlayerID = @@PlayerID

while exists (select PlayerID from DeptDrawingVouchers
where PlayerID = @@PlayerID
and DepartmentID = @@DepartmentID
and VouchersIssued < VouchersRedeemed )
begin
insert into DepartmentVouchers(PlayerID, DepartmentID, UserID,IssueDate)
(select PlayerID, DepartmentID, @@UserID, @@IssueDate from DeptDrawingVouchers
where PlayerID = @@PlayerID
and DepartmentID = @@DepartmentID)

update DeptDrawingVouchers
set VouchersIssued=VouchersIssued + 1
where PlayerID = @@PlayerID
and DepartmentID = @@DepartmentID

end

CREATE TABLE ##DeptVoucher(
DepartmentName char (50) not null,
PlayerID int not null,
FirstName varchar (30) not null,
LastName varchar (30) not null,
Address1A varchar (30) null,
City1 varchar (30) null,
State1 varchar (30) null,
Zip1 varchar (30) null,
UserID int not null,
IssueDate datetime
)ON [PRIMARY]

while exists (select PlayerID from DepartmentVouchers
where PlayerID = @@PlayerID
and Issued = 'N' )
begin
Insert into ##DeptVoucher
Select n.DepartmentName, p.Player_ID, p.FirstName, p.LastName, a.Address1A, a.City1, a.State1, a.Zip1,d.UserID, d.IssueDate
FROM CDS_Player p, CDS_Account a, DepartmentVouchers d, DepartmentConfig n
WHERE
p.Player_ID=a.Primary_ID
and p.Player_ID=d.PlayerID
and d.DepartmentID=n.DepartmentID
and p.Player_ID=@@PlayerID
/* and d.Issued = 'N'

update DepartmentVouchers
set Issued = 'Y'
where PlayerID = @@PlayerID
end

Select DepartmentName, PlayerID, FirstName, LastName, Address1A, City1, State1, Zip1, UserID, IssueDate
From ##DeptVoucher

Drop table ##DeptVoucher

return (0)
GO

Any assistance will be greatly appreciated. I’m totally stumped as to what’s going wrong.

X002548
Not Just a Number

15586 Posts

Posted - 2004-12-01 : 12:50:12

Whats with @@?

IS this 6.5?

Brett

8-)

ndn_24_7
Starting Member

23 Posts

Posted - 2004-12-01 : 12:52:26

I'm running SQL Server 2000. I thought @@ was declaring global varables.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-12-01 : 15:35:28

@@ is not a global variable, you can't declare global variables with T-SQL in SQL Server.
## is a "global" table.
Actually you almost NEVER want global variables and tables in your procedures.
I can't see the point of a global ##DeptVoucher table for example, a local #DeptVoucher table would be better.

Watch your keys...

update DeptDrawingVouchers
set VouchersRedeemed = @@VouchersRedeemed
where PlayerID = @@PlayerID

Do you really want to set VouchersRedeemed = @@VouchersRedeemed
for all the Player's departments ???

What Brett says,
@VouchersRedeemed looks better than @@VouchersRedeemed !

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources