Stored Procedure - SQL Server Forums

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

Author

Topic

ndn_24_7
Starting Member

23 Posts

Posted - 2004-11-24 : 17:09:38

Hello all,
I’m requesting some assistance with a stored procedure. There are total of 5 tables that interact with the stored procedure. The tables are listed below. The syntax might be a little off but the basic structure is a follows:

Tables

CDS_Player
Player_ID int (4) not null
FirstName varchar (30) not null
LastName varchar (30) not null

CDS_Account
Primary_ID int (4) not null [This is the same as player_ID in CDS_Player, which is joined to get the players address]
Address1A varchar (50) not null,
City1 varchar (30) null,
State1 varchar (30) null,
Zip1 varchar (30) null

DrawingVoucherNumbers
PromoID int (4)
PlayerID int (4)
VoucherNumber int (4),
Issued char (1),
IssueDate datetime,
UserID int (4)

DrawingVouchers
PromoID int 4
PlayerID int 4
TotalPoints int 4
VouchersEarned int 4
VouchersIssued int 4

VoucherConfig
PromoID int 4
PromotionName varchar 50
StartDate datetime 8
EndDate datetime 8
PtsPerVoucher int 4

Stored Procedure

CREATE PROCEDURE dbo.Report_IssueDrawingVoucher
@@PlayerId INT,
@@PromoID INT,
@@UserID INT
AS

Declare @@StartDate Datetime,
@@EndDate Datetime,
@@PtsPerVoucher INT,
@@IssueDate DateTime

select @@Startdate = Startdate from VoucherConfig where PromoID=@@PromoID
select @@EndDate = EndDate from VoucherConfig where PromoID=@@PromoID
select @@PtsPerVoucher = PtsPerVoucher from VoucherConfig where PromoID=@@PromoID
select @@IssueDate = getdate()

if not exists(select PlayerID from DrawingVouchers
where PromoID = @@PromoID
and PlayerID = @@PlayerID)

insert into DrawingVouchers
values(@@PromoID, @@PlayerID, 0, 0, 0)

Update DrawingVouchers
set TotalPoints = (select sum(PtsEarned) from CDS_StatDetail
where IDType = 'P'
and Meta_Id = @@PlayerID
and Gamingdate between @@StartDate and @@EndDate)
where PlayerID = @@PlayerID

Update DrawingVouchers
set VouchersEarned = TotalPoints/@@PtsPerVoucher
where PlayerID = @@PlayerID

while exists (select 1 from DrawingVouchers
where Playerid = @@PlayerID
and VouchersIssued < VouchersEarned)
BEGIN
insert into DrawingVoucherNumbers (PromoID, PlayerID, UserID, IssueDate)
(select PromoID, Playerid, @@UserID, @@IssueDate from DrawingVouchers
where PlayerID = @@PlayerID
and PromoId = @@PromoID)

update DrawingVouchers
set VouchersIssued = VouchersIssued + 1
where PlayerID = @@PlayerID
and PromoId = @@PromoID

END

CREATE TABLE ##Voucher (
PromotionName varchar (50) NOT NULL ,
Player_ID int NOT NULL ,
FirstName varchar (30) NOT NULL ,
LastName varchar (30) NOT NULL ,
Address1A varchar (50) NULL ,
City1 varchar (30) NULL ,
State1 varchar (30) NULL ,
Zip1 varchar (30) NULL ,
VoucherNumber int NOT NULL ,
UserID int NOT NULL ,
IssueDate datetime,
IssueTime varchar(10)
) ON [PRIMARY]

while exists (select 1 from DrawingVoucherNumbers
where Playerid = @@PlayerID
and Issued = 'N')
BEGIN
Insert into ##Voucher
Select n.PromotionName, p.Player_ID, p.FirstName, p.LastName, a.Address1A, a.City1, a.State1, a.Zip1, d.VoucherNumber, d.UserID, d.Issuedate,
cast(datepart(hh, IssueDate)as varchar) + ':' + cast(datepart(mi, IssueDate)as varchar) + ':' + cast(datepart(ss, IssueDate)as varchar)
from CDS_Player p, CDS_Account a, DrawingVoucherNumbers d, VoucherConfig n
where p.Player_ID = a.Primary_ID
and p.Player_ID = d.PlayerID
and d.PromoID = n.PromoID
and p.Player_ID = @@PlayerID
and d.Issued = 'N'

Update DrawingVoucherNumbers
set Issued = 'Y'
where PlayerID = @@PlayerID
END

select PromotionName, Player_ID, FirstName, LastName, Address1A, City1, State1, Zip1, VoucherNumber, UserID, IssueDate, IssueTime from ##Voucher

drop table ##Voucher

RETURN (0)
GO

This program prints one drawing voucher for every 300 points a player has earned within a specific date range. This date range is located in the VoucherConfig Table. The contents of the table are as follows:

PromoID PromotionName StartDate EndDate PtsPerVoucher
1 THE GREAT CASH GIVEAWAY IN JULY 6/15/2004 8/31/2004 300
2 THE GREAT FALL CASH GIVEAWAY 9/1/2004 10/30/2004 300
3 CRAZY CHRISTMAS CASH 11/1/2004 12/24/2004 300

This stored procedure interfaces with a Crystal Report to print the tickets. The problem I’m having is that some of the data in VouchersIssued in the DrawingVouchers table exceeds VouchersEarned. VouchersIssued should never exceed VouchersEarned. In some of the rows, I’m showing VouchersIssued being and outrageous number like 21988 when VouchersEarned is 20. I can’t figure out where the VouchersIssued is getting this outrageous number. Everything in the stored procedure looks right, but I haven’t had that much experience in writing stored procedure.
Any assistance will be greatly appreciated. If theres any more information needed please let me know

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-11-24 : 22:43:21

Could you provide some sample data and an expected resultset?

ndn_24_7
Starting Member

23 Posts

Posted - 2004-11-26 : 10:11:40

Sure,

Sample data in the DrawingVouchers table reads like this:

PromoID PlayerID TotalPts VouchersEarned VouchersIssued
3 654 8462 28 1099
3 26374 1229 4 4
3 1268 5172 17 17
3 17150 16 0 0
3 932 0 0 0
3 450 4864 16 16
3 851 1346 4 4
3 18632 286 0 0
3 1881 457 1 1
3 25092 3327 11 11
3 5444 5248 17 17
3 1677 5747 19 19
3 3911 2922 9 9
3 1069 1109 3 23052

As you can see, In some of the fields VouchersEarned exceeds vouchers issued. When ever we try to print vouchers where vouchers issued exceeds vouchers earned, it cause the crystal report to crash because it cant handle the excessive load.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-28 : 17:02:37

Put a check constraint on the DrawingVouchers table to ensure that VouchersEarned >= VouchersIssued.
ALTER TABLE DrawingVouchers ADD CONSTRAINT CHK_Issued_NotMoreThan_Earned CHECK(VouchersEarned >= VouchersIssued)

rockmoose

ndn_24_7
Starting Member

23 Posts

Posted - 2004-11-29 : 15:56:01

I tried setting VouchersIssued<=VouchersEarned,although it worked, I'm still not able to print vouchers because the constraint stops it from printing when the constraint is met. I need to figure out why its exceeding the amount earned. Looking at the stored procudure, I think the report is getting stuck in the While Exist loop. Is there a way to insert some code in the stored procedure to stop it when VOuchersEarned exceeds VouchersIssued?
Thanks FOr all your Help.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-29 : 16:31:43

Well that is good sign, is it not !?, the constraint keeps your data from becoming corrupted.

In your stored procedure, You pass in PlayerId and PromoID, which is the PK of DrawingVouchers table.
However in the code when you work with that DrawingVouchers table sometimes You use PlayerId + PromoId,
and sometimes You only use PlayerID !, I think will produce the unwanted behavior You are seeing.

For example: in the first while not exists(...) loop You do:
In the existance test you use only PlayerId,
but in the update clause You only use PlayerId + PromoId.... !?!?

rockmoose

ndn_24_7
Starting Member

23 Posts

Posted - 2004-12-01 : 10:41:15

I fixed the problems you pointed out, and it appears to work fine. Thank you for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources