Stored procedure in SQL

Question

1.00/5 (2 votes)

See more:

, +

Hello!
I have an assignment to do, so for any possibly help I'll be grateful.

I've created a database in SQL Server, with 3 tables (auctions, users and products).

Now I need to create a STORED PROCEDURE with 2 parameters: id of user and id of auctions. Procedure must check if user has possibility to do a bid (does he has enough bids). If he has enough bids, transaction will be run where first will be set a bid for a auction (the value of a bid field will raise for one) and then user bid will be decreased by one.

[From comment]
Table Auctions:

id productid lastbider bidvalue lastbid
1  1         2         3.00     70
2  2         1         3.00     23

Table Users:

id name   password bids
1  Peter  123       9
2  Sally  456      19

Table Product:

id name
1  Plasma TV
2  LapTop

Procedure name is usp_makebid

Here is what I've started doing:

SQL

CREATE PROCEDURE usp_makebid
( @userID  varchar 
  @auctionID varchar)
AS
BEGIN
    SELECT     auctions.id, users.id AS Expr1
    FROM         auctions INNER JOIN
                    users ON auctions.id = users.id

And I really don't know what to do next. :(
Can you just help me with what should I use ...

Posted 27-Sep-14 4:06am

Vexy

Updated 27-Sep-14 4:47am

George Jonsson

v3

Add a Solution

Comments

George Jonsson 27-Sep-14 10:11am

Could you present your table structure and some sample data?
(And please use the 'Improve question' widget to add more info.

Vexy 27-Sep-14 10:32am

Sure.
Table Auctions:
id productid lastbider bidvalue lastbid
1 1 2 3.00 70
2 2 1 3.00 23

Table Users:
id name password bids
1 Peter 123 9
2 Sally 456 19

Table Product:
id name
1 Plasma TV
2 LapTop

George Jonsson 27-Sep-14 10:43am

And what part of "Use the 'Improve question' widget" was unclear?

[no name] 27-Sep-14 10:19am

What do you mean "you don't know what to do next"? Shouldn't you be checking your data to see if the bidder has enough bids just like you have described?

Vexy 27-Sep-14 10:36am

Yeah, but how to write it as a code?! I am really a beginner. This probably looks dumb to you but it's a trouble for me. I only need to do it for school not to be a programmer. So any kind of help could be useful.

[no name] 27-Sep-14 10:54am

Well I don't need to do it at all, it's your homework assignment not mine. How do you think you would select the number of bids the user ID has in their account?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

/\jmot · Answer 1 · 2014-09-29T20:58:00

Try like this..

SQL

CREATE PROCEDURE usp_makebid
( @userID  varchar 
  @auctionID varchar)
AS
BEGIN
    

declare @HasBid int
declare @Opres int
set @HasBid=0

set @HasBid=select bids from Users where id=@userID

if @hasBid>0
begin
inseri into Auctions (column values)

select Users set Bids=bids-1 where id=@userID
set Opres=1
end
else
begin
set Opres=-1
end

select opres

Output code..

C#

if(opres==1)
{
response.write("Success!!")
}
else
{
}
response.write("You don't have enough bids!!please buy some");

Arora_Ankit · Answer 2 · 2014-10-07T00:22:00

Solution 3

Use this

SQL

IF EXISTS(SELECT id from Users WHERE bids>0 AND UserID=@userId)
BEGIN
    UPDATE Auctions
    SET bidvalue += 1,
        lastbider = @UseId
    WHERE id = @AuctionId;

    UPDATE Users
    SET bids -= 1
    WHERE id = @UseId;
end

Posted 7-Oct-14 0:22am

Arora_Ankit