--删除同名数据库Book
if exists (select * from master..sysdatabases where name ='Book')
drop database Book
go
--使用默认设置创建Book(默认在C盘SQLSERVER安装目录中创建)
create database Book
--创建图书库存表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Book_stock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Book_stock]
GO
CREATE TABLE [dbo].[Book_stock] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[book_name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[book_price] [money] NULL ,
[book_quantity] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Book_stock] WITH NOCHECK ADD
CONSTRAINT [PK_Book_stock] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
GO
--向“库存表”插入记录
use book
insert into book_stock (book_name,book_price,book_quantity)
values ('VB高手',15.50,100)
insert into book_stock (book_name,book_price,book_quantity)
values ('ASP指南',20.50,500)
insert into book_stock (book_name,book_price,book_quantity)
values ('JAVA基础',25.50,400)
insert into book_stock (book_name,book_price,book_quantity)
values ('VC之路',25.50,200)
insert into book_stock (book_name,book_price,book_quantity)
values ('C#基础',25.50,300)
----创建存储过程
--根据ID、图书名称,返回数量;
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[getQuantity]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[getQuantity]
GO
CREATE proc dbo.getQuantity ----输出参数
@bkid int,@bkname nvarchar(50),@result int output
as
select @result=book_quantity from book_stock
where id=@bkid and book_name=@bkname
--在查询分析器中测试存储过程
declare @n int
exec dbo.getQuantity 3,'JAVA基础',@n output
select @n