SQL 存储过程(1)

变量标识符应该由两部分组成：基本部分（内容）和前缀（数据类型）P41
比如：数据类型char  前缀chr  例子@chrFirstName

identity(起始值,步长) identity属性：自动增长列
比如identity(10,2)表示从10开始，每次+2

select f1（当前表的字段） as（生成别名关键字） txzxdm（当前字段f1的别名）
into（查询建表关键字） #xbuffer（生成的新表的表名,用#开头）
from wtzc40base..xbuffer
where xzbh = @p_xzbh

set nocount on 不返回实际的计数

第三章 存储过程的设计概念

存储过程的功能：
返回信息给调用者（途径：结果集，输出参数，返回值，全局游标）
修改数据库中数据
实现业务逻辑
控制数据访问权限
改善系统性能
降低网络流量
执行其他动作和操作（电子邮件，系统命令，进程，管理其他SQL对象）

新建一个存储过程
CREATE PROCEDURE test01 as
select * from server;
GO

在查询分析器里使用刚刚创建的存储过程
exec test01;

例子：返回Make列中等于输入参数@chvMake的所有记录。
  Create Procedure prGetEquitpent
   @chvMake varchar(50),
   @chvModel varchar(50)
as
  Select *
  from Equitpment
  where Make=@chvMake
  and Model=@chvModel

执行上面的存储过程(因参数是字符型所有用'',参数之间用“,”隔开)
exec prGetEquitpent 'Toshiba','Portege 7020CT'

删除存储过程
Drop Procedure prGetEquipment

修改存储过程(避免对权限和相关数据库对象产生不必要的影响)
alter procedure test01
as
select serverID from server
return 0
go

输出参数(关键字Output)
Create procedure prGetEqId_2
   @chvMake varchar(50),
   @chvModel varchar(50)
   @intEqId int output
as
  Select @intEqId=EquitpmentId
from Equipment
where Make=@chvMake
and Model=@chvModel
此程序存在一个潜在问题：满足该标准的值可能不仅一个。

返回值(return,将参数返回给调用者。仅能返回int类型的值，主要用于返回状态信息)
Create procedure prGetEqId_3
   @chvMake varchar(50),
   @chvModel varchar(50)
as
Declare @intEqId int             // Declare 变量声明

  Select @intEqId=EquitpmentId
from Equipment
where Make=@chvMake
and Model=@chvModel

Return @intEqId

return还可以接受一个整型表达式
Create procedure prGetEqId_3
   @chvMake varchar(50),
   @chvModel varchar(50)
as
  Return(Select EquitpmentId
from Equipment
where Make=@chvMake
and Model=@chvModel)

默认值
Create procedure prGetEqId_4
   @chvMake varchar(50)='%',
   @chvModel varchar(50)='%'
as
  Select *
from Equipment
where Make Like @chvMake         //Like字符匹配,%表示任意长度的字符串
and Model Like @chvModel

执行这个存储过程(按位置传递参数)
Exec prGetEqId_4 'T%','Portege%'

服务器将返回如下结果：EquipmentId  1
                      Make         Toshiba
                      Model        Portege7020CT
                      EqTypeId     1

同时省略两个参数：
Exec prGetEqId_4

服务器将返回如下结果：EquipmentId  1  2
                      Make         Toshiba  Sony
                      Model        Portege7020CT  TR17X
                      EqTypeId     1  3

按名称传递参数：（使代码更具可读性和维护性）
Exec prGetEqId_4 @Model - 'T%'  //T%为第二个参数,第一个参数默认为%

存储过程的语法

CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]

[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]

With Encryption 存储过程加密（不推荐）
With Recompile  SQL系统经济重新编译（适合开发人员）
[ ; number ] 存储过程组。

 

set ansi_nulls [on/off]    跟null等相关

set   ansi_nulls   off  
可以使用=做比较 select   *   from   a   where   price=null   若此时a中有price為NULL的就可以选出來  
而在set   ansi_nulls   on时候  
只能写                  select   *   from   a   where   price   is   null

set quoted_identifier [on/off]    跟标示符相关

   set quoted_identifier on      “当 SET QUOTED_IDENTIFIER 为 ON 时，标识符可以由双引号分隔，而文字必须由单引号分隔。当 SET

QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，且必须遵守所有 Transact-SQL 标识符规则。”

SET QUOTED_IDENTIFIER ON
SELECT * FROM "USER"  WHERE a='netasp'
SET QUOTED_IDENTIFIER ON
SELECT * FROM [USER] WHERE a='netasp'
SET QUOTED_IDENTIFIER OFF
SELECT * FROM [USER]   WHERE a="netasp"
SET QUOTED_IDENTIFIER OFF
SELECT * FROM [USER]  WHERE a= 'netasp'