以下内容为从某IT培训的SQL课件中找出来的，在这里做下笔记：


系统自带的存储过程


sp_databases

列出服务器上的所有数据库。

sp_helpdb

报告有关指定数据库或所有数据库的信息

sp_renamedb

更改数据库的名称

sp_tables

返回当前环境下可查询的对象的列表

sp_columns

回某个表列的信息

sp_help

查看某个表的所有信息

sp_helpconstraint

查看某个表的约束

sp_helpindex

查看某个表的索引

sp_stored_procedures

列出当前环境中的所有存储过程。

sp_password

添加或修改登录帐户的密码。

sp_helptext

显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。


1、为方使测试，将测试坏境代码给出来，

use master
go
xp_cmdshell 'mkdir d:\project', NO_OUTPUT  --创建文件夹project,xp_cmdshell为系统存储过程
--检验数据库是否存在，如果为真，删除此数据库--
IF EXISTS(SELECT NAME FROM master.dbo.SYSDATABASES WHERE NAME=N'stuDB')
DROP DATABASE stuDB
GO
--创建数据库--
CREATE DATABASE stuDB
ON
(NAME=N'stuDB',
 FILENAME='e:\project\stuDB.mdf',
 SIZE=5mb,
 MAXSIZE=10mb,
 FILEGROWTH=15%)
LOG ON
 (NAME=N'stuDB_log',
  FILENAME='e:\project\stuDB_log.ldf',
  SIZE=2mb,
  MAXSIZE=4mb,
  FILEGROWTH=15%)
GO
USE stuDB
--判断此表是否存在，如果为存在，删除此表--
IF EXISTS(SELECT NAME FROM stuDB.dbo.SYSOBJECTS WHERE NAME=N'stuInfo')
DROP TABLE stuInfo
GO
--创建主表stuInfo--
CREATE TABLE stuInfo
(stuName    NVARCHAR(20)    NOT NULL,
 stuNo      NCHAR(6)        NOT NULL,
 stuSex     NCHAR(4)        NOT NULL,
 stuAge     SMALLINT        NOT NULL,
 stuSeat    SMALLINT        IDENTITY(1,1),
 stuAddress NTEXT)   
GO
--为主表stuInfo创建约束--
ALTER TABLE stuInfo
  ADD CONSTRAINT PK_stuNo      PRIMARY KEY(stuNo),
      CONSTRAINT CK_stuNo      CHECK(stuNo LIKE 'S253[0-9][0-9]'),
      CONSTRAINT CK_stuSex     CHECK(stuSex='男' OR stuSex='女'),
      CONSTRAINT CK_stuAge     CHECK(stuAge BETWEEN 15 AND 40),
      CONSTRAINT CK_stuSeat    CHECK(stuSeat<=30),
      CONSTRAINT DF_stuAddress DEFAULT ('地址不详') FOR stuAddress
GO
USE stuDB
--判断此表是否存在，如果存在，删除此表--
IF EXISTS(SELECT NAME FROM stuDB.dbo.SYSOBJECTS WHERE NAME=N'stuMarks')
DROP TABLE stuMarks
GO
--创建从表stuMarks--
CREATE TABLE stuMarks
(ExamNo CHAR(7) NOT NULL,
 stuNo NCHAR(6) NOT NULL,
 writtenExam SMALLINT NOT NULL,
 LabExam SMALLINT NOT NULL)
GO
--为从表stuMarks创建约束--
ALTER TABLE stuMarks
   ADD CONSTRAINT PK_ExamNo         PRIMARY KEY(ExamNo),
       CONSTRAINT CK_ExamNo         CHECK(ExamNo LIKE 'S2718[0-9][0-9]'),
       CONSTRAINT FK_stuNo          FOREIGN KEY(stuNo) REFERENCES stuInfo(stuNo),
       CONSTRAINT CK_writtenExam    CHECK(writtenExam BETWEEN 0 AND 100),
       CONSTRAINT DF_writtenExam    DEFAULT 0 FOR writtenExam,
       CONSTRAINT CK_LabExam        CHECK(LabExam BETWEEN 0 AND 100),
       CONSTRAINT DF_LabExam        DEFAULT 0 FOR LabExam
GO
--为stuInfo插入数据--
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress)VALUES('张秋丽','s25301','男',18,'北京海淀')
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('李斯文','s25303','女',22,'河南洛阳')
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge) VALUES('李文才','s25302','男',31)
INSERT INTO stuInfo(stuName,stuNo,stuSex,stuAge,stuAddress) VALUES('欧阳俊雄','s25304','男',28,'新疆威武哈')
GO
--为stuMarks插入数据--
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('S271811','s25303',80,58)
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('S271813','s25302',50,90)
INSERT INTO stuMarks(ExamNo,stuNo,writtenExam,LabExam) VALUES('S271816','s25301',77,82)
GO



2、常用系统存储过程使用

-- Purpose: 常用系统存储过程使用
EXEC sp_databases  --列出当前系统中的数据库
EXEC  sp_renamedb 'Northwind','Northwind1'--改变数据库名称(单用户访问)
USE stuDB
GO
EXEC sp_tables  --当前数据库中查询的对象的列表
EXEC sp_columns stuInfo  --返回某个表列的信息
EXEC sp_help stuInfo  --查看表stuInfo的信息
EXEC sp_helpconstraint stuInfo --查看表stuInfo的约束
EXEC sp_helpindex stuMarks  --查看表stuMarks的索引
EXEC sp_helptext 'view_stuInfo_stuMarks' --查看视图的语句文本
EXEC sp_stored_procedures  --返回当前数据库中的存储过程列表


2.1、使用xp_cmdshel扩展存储过程的使用

Purpose: xp_cmdshell扩展存储过程的使用
USE master
GO
/*---创建数据库bankDB，保存在D:\bank---*/
EXEC xp_cmdshell 'mkdir d:\bank', NO_OUTPUT  --创建文件夹D:\bank
--创建建库bankDB
IF exists(SELECT * FROM sysdatabases WHERE name='bankDB')
  DROP DATABASE bankDB
GO
CREATE DATABASE bankDB
 ON
 (
  NAME='bankDB_data',
  FILENAME='d:\bank\bankDB_data.mdf',
  SIZE=1mb,
  FILEGROWTH=15%
 )
 LOG ON
 (
  NAME= 'bankDB_log',
  FILENAME='d:\bank\bankDB_log.ldf',
  SIZE=1mb,
  FILEGROWTH=15%
 )
GO
EXEC xp_cmdshell 'dir D:\bank\' --查看文件


3、不带参数的存储过程

USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
  AS
    DECLARE @writtenAvg float,@labAvg float --笔试和机试平均分变量
    SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) FROM stuMarks
    print '笔试平均分：'+convert(varchar(5),@writtenAvg)
    print '机试平均分：'+convert(varchar(5),@labAvg)
    IF (@writtenAvg>70 AND @labAvg>70)
       print '本班考试成绩：优秀'
    ELSE
       print '本班考试成绩：较差'
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<60 OR labExam<60
GO
/*---调用存储过程----*/
EXEC proc_stu  --调用存储过程的语法：EXEC 过程名 [参数]


4、带参数的存储过程

例1

USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
  @writtenPass int,
  @labPass int
  AS
    print '笔试及格线：'+convert(varchar(5),@writtenPass)
    print '机试及格线：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
GO
/*---调用存储过程----*/
--假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分。
EXEC proc_stu 60,55  --或这样调用：EXEC proc_stu @labPass=55,@writtenPass=60

例2


USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
  @writtenPass int=60,
  @labPass int=60
  AS
    print '笔试及格线：'+convert(varchar(5),@writtenPass)
       + '   机试及格线：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
GO
/*---调用存储过程----*/
EXEC proc_stu   --都采用默认值：笔试和机试及格线都为60分
EXEC proc_stu 64  --机试采用默认值：笔试及格线64分，机试及格线60分。
EXEC proc_stu 60,55   --都不采用默认值：笔试及格线60分，机试及格线55分。
--错误的调用方式：EXEC proc_stu  ,55  --希望笔试采用默认值，机试及格线55分
--正确的调用方式：EXEC proc_stu @labPass=55   --笔试采用默认值，机试及格线55分


5、带输出参数的存储过程

USE stuDB
GO
/*---检测是否存在：存储过程存放在系统表sysobjects中---*/
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'proc_stu' )
  DROP PROCEDURE  proc_stu
GO
/*---创建存储过程----*/
CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT, --OUTPUT关键字，否则视为输入参数
  @writtenPass int=60,  --默认参数放后
  @labPass int=60       --默认参数放后
  AS
    print '笔试及格线：'+convert(varchar(5),@writtenPass)
       + '   机试及格线：'+convert(varchar(5),@labPass)
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM  stuInfo
      INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo
         WHERE writtenExam<@writtenPass OR labExam<@labPass
    /*--统计并返回没有通过考试的学员人数--*/
    SELECT @notpassSum=COUNT(stuNo)FROM stuMarks
      WHERE writtenExam<@writtenPass OR labExam<@labPass
                                                                                                           
GO
/*---调用存储过程----*/
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64   --机试及格线采用默认值：笔试及格线64分，机试及格线60分。
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
GO