(一)存储过程几点注意:
(1)写select语句的时候要注意查询条件顺序,select语句按照从前往后顺序执行查询,所以要注意查询条件前后顺序,一般单表过滤放在查询体前面,连接表过滤放在查询体后面,可以提高效率。
(2)如果有select表建有索引,优先考虑索引,因为索引可以提高查询性能(但是索引不能建太多,索引是牺牲一定的性能为代价的)。
(3)查询语句 from 表的后面建议添加 with(nolock),防止死锁发生,提高执行效率。
(4)建议尽可能使用 “=” 代替 “<>” 或 “in” ,可以提高查询效率。
(5)处理查询条件 @Condition 的时候为了防止 “ and ” 造成错误,可以在前面添加 "where 1=1 "。
(6)存储过程的命名方式一般采取表名+操作。如Doc_Attribute_Select、Archive_GetDefaultArchiveNum、Archive_IsExistArchiveNum,这样逻辑显得比较清晰。
(7)select语句尽量写具体字段,不要 select × ,这样可以减少查询操作的解析时间,提高效率。
(8)是否存在存储过程,最好用存储过程自动生成的drop,检索是否存在之后一定要写上GO,不然许多脚本执行的时候会报错。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Doc_Receive_Select]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Doc_Receive_Select]
GO
(9)创建存储过程的能具体的参数尽量具体,尽量不要全部 @Condition,注意 @Sort 要分离开来,这样逻辑清晰,便于维护。
CREATE PROCEDURE [dbo].[Doc_Receive_Select]
@ApplicationName varchar(50),
@SubSystemName varchar(50),
@DealName varchar(200),
@Condition varchar(1000),
@sort varchar(200)
as
DECLARE @SqlString NVARCHAR(2000)
begin
SET @SqlString = 'SELECT ……
WHERE ExecutorName like ''%'+ @DealName+'%''
and Doc_Receive.IsDeleted=''0''
and transfer_transactInfoMax.BizID =Transfer_TransactInfo.BizID
and transfer_transactInfoMax.TRANMAXID=Transfer_TransactInfo.ID
and Transfer_TransactInfo.ApplicationName='''+ @ApplicationName + '''
and Transfer_TransactInfo.SubSystemName = '''+ @SubSystemName + ''' '
SET @SqlString = @SqlString + @Condition+@sort
end
EXEC sp_executesql @SqlString
注:在数据库里单引号'必须采用两个单引号的形式''才有效。 两个分开的单引号之间代表字符串' …… '。
(10)select语句可以用常量赋值查询字段,方便自己定义参数满足需要。
如:select ArchiveID,''DocSend'' as DocType,''0'' as ReceiveType
(二)存储过程常用语句:
(1)可以用下面语句进行条件选择。
if
begin
……
end
else
begin
……
end
(2)用 select 判断数值是否存在
CREATE PROCEDURE [dbo].[Archive_IsExistArchiveNum]
……
as
declare @count int
select @count = count(ArchiveID) from ArchiveDocSend with(nolock) WHERE ……
if (@count <=0)
select @count = count(ArchiveID) from ArchiveDocRec with(nolock) WHERE ……
select @count
场景描述:
检索表A,如果存在数值,则返回;如果不存在,继续检索表B;返回检索结果是否存在。
(3)用 select 比较大小
CREATE PROCEDURE [dbo].[Archive_GetDefaultArchiveDocNum]
……
AS
declare @MaxNumSend int,@MaxNumRec int
select @MaxNumSend = max(convert(int,DocNum)) from ArchiveDocSend with(nolock) WHERE ……
select @MaxNumRec = max(convert(int,DocNum)) from ArchiveDocRec with(nolock) WHERE ……
if (@MaxNumSend is null)
set @MaxNumSend=0
if (@MaxNumRec is null)
set @MaxNumRec=0
IF(@MaxNumSend >=@MaxNumRec)
select @MaxNumSend
else
select @MaxNumRec
场景描述:从表A获取最大值max(A),从表B获取最大值max(B),返回两者中最大值。
(4)循环语句:
declare @NumLoop int
SELECT @NumLoop = DATEDIFF(Month, @StartTime , @EndTime)
declare @i int
select @i=0
while(@i<@NumLoop+1)
begin
……
select @i=@i+1
end
(5)用 case 进行选择赋值
(case TransactState when ''未办结'' then ''※'' when ''已办结'' then ''☆'' when ''待归档'' then ''★'' end) as TransactState
(6)使用 (select……)UNION (select……)形式连接两个查询体
select Top 12 * from (
select ArchiveID,…… 'DocSend' as DocType,'0' as ReceiveType
FROM ArchiveDocSend with(nolock) WHERE ……
union
select ArchiveID,…… 'DocRec' as DocType,'1' as ReceiveType
FROM ArchiveDocRec with(nolock) WHERE ……)
as dt order by ……
注:几个查询体一起union的时候,如果第1个查询体和第2个查询体的列名不一样,那么查询的时候对应位置以第一列为准。
(三)select 查询关键字
distinct、top
max()、min()、avg()、sum()、count()
len()
like、in
and、or
order by
group by
(四)数据操作
插入操作:
INSERT INTO [dbo].[OrgAreaRecord]
([OrgID],
[OrgName],
…… )
VALUES
(@OrgID,
@OrgName,
…… )
insert into 目标数据表 select * from 源数据表
更新操作:
UPDATE [dbo].[ArchiveNumRule]
SET [OrgID] =@OrgID,
[OrgName] =@OrgName,
……
WHERE RuleID=@RuleID
删除语句:
DELETE FROM [dbo].[ArchiveNumRule]
WHERE RuleID=@RuleID
(五)表结构操作
CREATE 创建新表
ALTER 更该表结构
DROP 删除表结构