Need Help setting up a stored procedure

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 2005 Forums

Transact-SQL (2005)

Need Help setting up a stored procedure

Author

Topic

nocty
Starting Member

1 Post

Posted - 2008-12-12 : 03:37:35

Hello all,

I am a Student and our class has been assigned a project that deals with the ticket system (raffles) for the local Ronald McDonald House. I can not figure out the logic for the tickets at all. We have created the following tables that deal with the tickets.

Ticket
Ticket_ID
Ticket_Year (What year was the prize raffled off in)
Ticket_Num (the ticket created)
Gift_Type (Was the ticket a gift? This is a bit data_type)
FK - Donor_ID (relates back to the donor so the system knows who bought the ticket)
FK-Ticket_Price_ID
FK-Ticket_Status_ID

Ticket_Price
Ticket_Price_ID
Ticket_Quanity_Low (This is a lower tier for the quanity of tickets produced)
Ticket_Quanity_High (This is a upper tier for the quanity of tickets produced)
Ticket_Price (The price of the ticket in the range it falls in)*I'll explain later

Ticket_Status
Ticket_Status_ID
Ticket_Status_Description(printed/mailed, reprinted, cancled... ect)

So, the way the system should work is as follows. The person taking the ticket order should have the option to input how many tickets the donor wants which will generate the number of tickets given and the dollar amount of the tickets. The system should also allow the employee to enter the dollar amount given from the donor which will generate how many tickets will be printed.

The way the ticket_Quanity_Low and ticket_quanity_High works is it is suppose to be a range so if i donor asks for 3 tickets and the price bracket range is from 1(low) to 4(high) and the price would be $3.00, 5(low) to 14(high) would be $2, 15(low) to 39(high) would be $1.33, 40(low) to 9,999,999(high) would be $1.25

I'm so lost on this.

I think it has to be something like

select * from Ticket_Price
Where (Amount_Low <=@Ticket_Count) AND (Amount_High >=@Ticket_Count)

Then I need to insert a while loop which would Insert values in tickets. So I I wanted 200 tickets it would be

declare ticketcount As ticketcount.text (Which would be the 200 tickets?)

While ticketcount >= ticket_Id
Insert into tickets Values(@ID, @year, @isgift, @donor_ID, @Status)

Anyhelp on how to do this correctly would be so useful!

Thanks,

Justin

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 07:36:32

You should avoid looping, try to find a way to do this as a set.
If you make ticketid an incrementing number won't values be same for a single insert? If yes do as single insert.
--Rough draft you will need to finish it yourself
Create procedure pTickets (@NumTickets int,@Year datetime,@Gift varchar(250),@Donor varchar(250),@Status varchar(250))

declare @Price varchar(50)
--Number of Tickets needed for each price bracket
set @Price = (Select PriceId from ticket_Price where Ticket_Quanity_Low <=@NumTickets and Ticket_Quanity_High>@NumTickets)

--Ticket_Num (should probably be an identity - incrementing number)
INSERT INTO Ticket (Ticket_Year,Gift_Type,Donor_ID,Ticket_Price_ID,Ticket_Status_ID
VALUES
(@Year,@Gift,@Donor,@Price,@Status)

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-12-12 : 07:40:38

One note:
having the price refer to a priceid for a range, if the price changes for a range in future this will ripple back to old tickets already sold for a different price.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources