SQL中的存储过程 [英] Stored procedure in SQL

问题描述

你好！

我有一个任务要做，所以对于任何可能的帮助，我将不胜感激。



我已经在SQL Server中创建了一个数据库，有3个表（拍卖，用户和产品）。



现在我需要创建一个带有2个参数的存储过程：用户ID和拍卖的ID。程序必须检查用户是否有可能进行出价（他是否有足够的出价）。如果他有足够的出价，交易将在首先设置竞价出价（出价字段的价值将提高一个），然后用户出价将减少一个。



[来自评论]

表拍卖：

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

程序名称为usp_makebid



以下是我开始做的事情：

Procedure name is usp_makebid

Here is what I've started doing:

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 ...

推荐答案

试试这样..

Try like this..

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..

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

使用此

Use this

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

这篇关于SQL中的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！