--添加用户信息，并有错误检查！
-------------------------------------------------------------------------------
--
-- Stored Procedures
--
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
-- upAccountAdd
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountAdd
(
@userid varchar(80),
@password varchar(80),
@email varchar(80),
@firstname varchar(80),
@lastname varchar(80),
@addr1 varchar(80),
@addr2 varchar(40),
@city varchar(80),
@state varchar(80),
@zip varchar(20),
@country varchar(20),
@phone varchar(80),
@langpref varchar(80),
@favcategory varchar(80),
@mylistopt int,
@banneropt int
)
AS
-- SET NOCOUNT to ON and no longer display the count message
SET NOCOUNT ON
DECLARE @CurrentError int
-- start transaction, inserting into three tables
BEGIN TRANSACTION
-- create a login
INSERT INTO Signon (username, [password])
VALUES (@userid, @password)
select @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- create a new profile for the user
INSERT INTO [Profile] (userid, langpref, favcategory, mylistopt, banneropt)
VALUES (@userid, @langpref, @favcategory, @mylistopt, @banneropt)
select @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- upadte the account record
INSERT INTO Account (userid, email, firstname, lastname, status, addr1, addr2, city, state, zip, country, phone)
VALUES (@userid, @email, @firstname, @lastname, 'OK', @addr1, @addr2, @city, @state, @zip, @country, @phone)
select @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- end of transaction
COMMIT TRANSACTION
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF
-- return 0 to indicate success, otherwise the raised error will be returned
RETURN 0
ERROR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN @CurrentError
GO
--根据用户名返回用户地址等信息！
-------------------------------------------------------------------------------
-- upAccountGetAddress
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountGetAddress
(
@userID varchar(80)
)
AS
SELECT email, firstname, lastname, status, addr1, addr2, city, state, zip, country, phone
FROM Account
WHERE userid = @userID
GO
--返回用户详细信息
-------------------------------------------------------------------------------
-- upAccountGetDetails
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountGetDetails
(
@userID varchar(80)
)
AS
SELECT Account.userid, Account.email, Account.firstname, Account.lastname,
Account.status, Account.addr1, Account.addr2, Account.city,
Account.state, Account.zip, Account.country, Account.phone,
Profile.langpref, Profile.favcategory, Profile.mylistopt, Profile.banneropt
FROM Account INNER JOIN Profile ON Account.userid = Profile.userid
WHERE Account.userid = @userID
GO
--用户登入
-------------------------------------------------------------------------------
-- upAccountLogin
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountLogin
(
@username varchar(25),
@password varchar(25),
@CustomerID varchar(25) OUTPUT
)
AS
SELECT @CustomerID = username
FROM signon
WHERE username = @username AND password = @Password
-- if the select didn't return any rows in the result,
-- then set the customer id to an empty string to indicate a login
-- failure, otherwise the username will be returned to indicate
-- a successful login
IF @@Rowcount < 1
SELECT @CustomerID = ''
GO
-------------------------------------------------------------------------------
-- upAccountUpdate
-------------------------------------------------------------------------------
CREATE PROCEDURE upAccountUpdate
(
@userid varchar(80),
@email varchar(80),
@firstname varchar(80),
@lastname varchar(80),
@addr1 varchar(80),
@addr2 varchar(40),
@city varchar(80),
@state varchar(80),
@zip varchar(20),
@country varchar(20),
@phone varchar(80),
@langpref varchar(80),
@favcategory varchar(80),
@mylistopt int,
@banneropt int
)
AS
-- SET NOCOUNT to ON and no longer display the count message.
SET NOCOUNT ON
DECLARE @CurrentError int
-- start transaction, updating two tables
BEGIN TRANSACTION
UPDATE Account SET email = @email, firstname = @firstname, lastname = @lastname, addr1 = @addr1, addr2 = @addr2, city = @city,
state = @state, zip = @zip, country = @country, phone = @phone
WHERE userid = @userid
select @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
UPDATE Profile SET langpref = @langpref, favcategory = @favcategory, mylistopt = @mylistopt, banneropt = @banneropt
WHERE userid = @userid
select @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- end of transaction
COMMIT TRANSACTION
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF
-- return 0 to indicate success, otherwise the raised error will be returned
RETURN 0
ERROR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN @CurrentError
GO
-------------------------------------------------------------------------------
-- upCategoryGetList
-------------------------------------------------------------------------------
CREATE PROCEDURE upCategoryGetList
AS
SELECT catid, [name]
FROM Category
ORDER BY catid
GO
-------------------------------------------------------------------------------
-- upInventoryAdd
-------------------------------------------------------------------------------
CREATE PROCEDURE upInventoryAdd
(
@itemid char(10),
@qty int
)
AS
-- insert into the item table
INSERT INTO Inventory (itemid, qty)
VALUES (@itemid, @qty)
GO
-------------------------------------------------------------------------------
-- upInventoryGetList
-------------------------------------------------------------------------------
/******************************************************************************
Get the qty for the items in the xml document.
Sample usage:
declare @xml varchar(8000)
set @xml =
'
<Inventory>
<LineItem itemid="EST-1" />
<LineItem itemid="EST-2" />
<LineItem itemid="EST-3" />
</Inventory>
'
exec upInventoryGetList @xml
*******************************************************************************/
CREATE PROCEDURE upInventoryGetList
(
@xml varchar(8000)
)
AS
DECLARE @idoc int -- xml doc
DECLARE @orderid int -- new order
-- parse xml doc
EXEC sp_xml_preparedocument @idoc output, @xml
-- return qty for items specified in xml
SELECT i.itemid, i.qty
FROM OPENXML(@idoc, '/Inventory/LineItem')
WITH (itemid char(10)) AS x
INNER JOIN Inventory AS i
ON x.itemid = i.itemid
-- done with xml doc, remove it from memory
EXEC sp_xml_removedocument @idoc
GO
-------------------------------------------------------------------------------
-- upItemAdd
-------------------------------------------------------------------------------
CREATE PROCEDURE upItemAdd
(
@itemid char(10),
@productid char(10),
@listprice decimal(10, 2),
@unitcost decimal(10, 2),
@supplier int,
@status char(2),
@attr1 varchar(80),
@attr2 varchar(80),
@attr3 varchar(80),
@attr4 varchar(80),
@attr5 varchar(80)
)
AS
-- insert into the item table
INSERT INTO Item (itemid, productid, listprice, unitcost, supplier, status, attr1, attr2, attr3, attr4, attr5)
VALUES (@itemid, @productid, @listprice, @unitcost, @supplier, @status, @attr1, @attr2, @attr3, @attr4, @attr5)
GO
-------------------------------------------------------------------------------
-- upItemGetDetails
-------------------------------------------------------------------------------
CREATE PROCEDURE upItemGetDetails
(
@itemID char(10),
@price smallmoney output,
@qty int output,
@itemName varchar(80) output,
@itemAttr varchar(80) output,
@desc varchar(255) output
)
AS
SELECT @price = Item.listprice, @qty = Inventory.qty,
@itemName = Product.name, @itemAttr = Item.attr1, @desc = Product.descn
FROM Item INNER JOIN
Inventory ON Item.itemid = Inventory.itemid INNER JOIN
Product ON Item.productid = Product.productid
WHERE (Item.itemid = @itemID)
GO
-------------------------------------------------------------------------------
-- upItemGetList
-------------------------------------------------------------------------------
CREATE PROCEDURE upItemGetList
(
@prodid varchar(10)
)
AS
SELECT itemid, listprice, attr1
FROM Item
WHERE productid = @prodid
GO
-------------------------------------------------------------------------------
-- upItemGetList_ListByPage
-------------------------------------------------------------------------------
CREATE PROCEDURE upItemGetList_ListByPage
(
@prodid varchar(10),
@nCurrentPage int,
@nPageSize int,
@totalNumResults int output
)
AS
-- we are creating a temporary table to store the currently
-- selected page of data. a rowid field has been added to allow
-- us to track which page we are on (the itemid didn't work
-- in this case because it is a character data type and it is
-- much easier to calculate the paging with an int)
CREATE TABLE #SearchResultsTempTable
(
rowid int IDENTITY PRIMARY KEY,
itemid char(10) NOT NULL,
listprice decimal(10, 2) NULL,
attr1 varchar(80) NULL,
)
-- copy the search results into the temporary table
INSERT INTO #SearchResultsTempTable (itemid, listprice, attr1)
SELECT itemid, listprice, attr1
FROM Item
WHERE productid = @prodid
-- always return the total number of items found in the search
SELECT @totalNumResults = @@ROWCOUNT
-- calculate the current page
DECLARE @nFirstPageRecord int
DECLARE @nLastPageRecord int
SELECT @nFirstPageRecord = (@nCurrentPage - 1) * @nPageSize
SELECT @nLastPageRecord = ((@nCurrentPage * @nPageSize) + 1)
-- select the correct page of data with the given page size
SELECT itemid, listprice, attr1
FROM #SearchResultsTempTable
WHERE (rowid > @nFirstPageRecord) AND (rowid < @nLastPageRecord)
GO
-------------------------------------------------------------------------------
-- upOrdersAdd
-------------------------------------------------------------------------------
/******************************************************************************
Add order to database. Example of using this stored proc is shown below.
declare @xml varchar(8000)
set @xml =
'
<Orders
userid="j2ee" orderdate="1/1/2001"
shipaddr1="1234 West Branch" shipaddr2=""
shipcity="Liveoak" shipstate="Texas"
shipzip="12345" shipcountry="USA"
billaddr1="5678 East Branch" billaddr2="Building C"
billcity="Madrone" billstate="Utah"
billzip="99999" billcountry="USA"
courier="UPS" totalprice="57.50"
billtofirstname="Fred" billtolastname="Derfy"
shiptofirstname="Bob" shiptolastname="Black"
creditcard="111-222-333" exprdate="9/2002"
cardtype="Visa" locale="US_en">
<LineItem itemid="EST-1" linenum="1" quantity="4" unitprice="5.00" />
<LineItem itemid="EST-2" linenum="2" quantity="5" unitprice="7.00" />
<LineItem itemid="EST-3" linenum="3" quantity="2" unitprice="1.25" />
</Orders>
'
exec upOrderAdd @xml
*******************************************************************************/
CREATE PROCEDURE upOrdersAdd
(
@xml varchar(8000)
)
AS
DECLARE @idoc int -- xml doc
DECLARE @orderid int -- new order
-- parse xml doc
EXEC sp_xml_preparedocument @idoc output, @xml
SET NOCOUNT ON
DECLARE @CurrentError int
-- start transaction, updating three tables
BEGIN TRANSACTION
-- add new order to Orders table
INSERT INTO Orders
SELECT userid, orderdate, shipaddr1, shipaddr2, shipcity, shipstate,
shipzip, shipcountry, billaddr1, billaddr2, billcity, billstate, billzip,
billcountry, courier, totalprice, billtofirstname, billtolastname,
shiptofirstname, shiptolastname, creditcard, exprdate, cardtype, locale
FROM OpenXML(@idoc, '/Orders')
WITH Orders
-- check for error
SELECT @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- get new order id
SELECT @orderid = @@IDENTITY
-- add line items to LineItem table
INSERT INTO LineItem
SELECT @orderid, linenum, itemid, quantity, unitprice
FROM OpenXML(@idoc, '/Orders/LineItem')
WITH LineItem
-- check for error
SELECT @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- add status to OrderStatus table
INSERT INTO OrderStatus
SELECT @orderid, @orderid, getdate(), 'P'
-- check for error
SELECT @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- update inventory
UPDATE Inventory
SET Inventory.qty = Inventory.qty - LineItem.quantity
FROM OpenXML(@idoc, '/Orders/LineItem')
WITH LineItem
WHERE Inventory.itemid=LineItem.itemid
-- check for error
select @CurrentError = @@Error
IF @CurrentError != 0
BEGIN
GOTO ERROR_HANDLER
END
-- end of transaction
COMMIT TRANSACTION
SET NOCOUNT OFF
-- done with xml doc
EXEC sp_xml_removedocument @idoc
-- return the new order
RETURN @orderid
ERROR_HANDLER:
ROLLBACK TRANSACTION
SET NOCOUNT OFF
RETURN 0
GO
-------------------------------------------------------------------------------
-- upOrdersGetStatus
-------------------------------------------------------------------------------
CREATE PROCEDURE upOrderStatusGet
(
@orderid integer,
@OrderStatus char(2) OUTPUT
)
AS
SELECT @OrderStatus = status
FROM OrderStatus
WHERE (orderid = @orderid);
GO
9053
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
