![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
这个存储过程的作用是自动生成编号,比如我们在数据库中有个**票编号字段,该存储过程可以生成格式为头(自己定义的一个头,比如AA)+日期(形如20070401)+6位递增整数(形如002254),并且每到了新的一年后面的6位整数便会归0重新开始。其中用到了动态执行sql的方法。参数有四个,需要给出表名称,字段名称,头和一个返回值。
=============================================
-- Author: <vagerent>
-- Create date: <2006-12-14>
-- Description: <获取编码,形如HEAD20060512000001;(头+日期+6位整数)后面为六位,每新的一年编号重新
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
归0>
-- =============================================
Create PROCEDURE [dbo].[mp_GetPKNo]
@sTableName varchar(50),
@sFieldName varchar(50),--目标字段
@sHead varchar(50),--前缀,如Head等
@sPK varchar(50) out --输出
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
AS
BEGIN
declare @dToday datetime,@sToday varchar(10),
@sYear varchar(10),@sSubID varchar(20),@sSql nvarchar(1000);--动态sql一定要用
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
nvarchar!!!
set @dToday = getdate();
set @sToday = Convert(varchar,Year(@dToday))
+SubString(Convert(varchar,Month(@dToday)+100),2,2)
+SubString(Convert(varchar,Day(@dToday)+100),2,2);
--select @sToday--形如20060512
set @sYear=Convert(varchar(10),Year(getdate()))
--select @sYear--形如2006
declare @sHead1 varchar(30),@iHeadLength int;
set @sHead1=@sHead+@sYear;--形如HEAD2006
--select @sHead1;--形如HEAD2006
set @iHeadLength=Len(@sHead1);
--select @iHeadLength;--形如6
set @sSql = N'select @sSubID=Max(SubString('
+@sFieldName+','+Convert(varchar(10),@iHeadLength+5)+',6)) from '
+@sTableName+' where SubString('+@sFieldName+',1,'
+Convert(varchar(10),@iHeadLength)+')='''+@sHead1+'''';
--select @sSql;--形如select
..
exec sp_executesql @sSql,N'@sSubID varchar(20) output',@sSubID output;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
if ((@sSubID='') or (@sSubID is null))
set @sSubID='000001'
else
begin
declare @iSubID int;
set @iSubID=Convert(int,@sSubID)+1000001;
set @sSubID=SubString(Convert(varchar,@iSubID),2,6)
end
--select @sSubID;
set @sPK=@sHead+@sToday+@sSubID;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
return
END
--Demo 1:
use northwind
go
set statistics IO on
go
select count(*) from northwind.dbo.employees
go
set statistics IO off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
use northwind
go
exec sp_spaceused employees
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 2:
set statistics time on
go
select count(*) from northwind.dbo.employees
go
set statistics time off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 3:
set showplan_text on
go
select count(*) from northwind.dbo.employees
go
set showplan_text off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 4:
set nocount on
go
select count(*) from northwind.dbo.employees
go
set nocount off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 5查询单条sql语句的执行时间:
declare @start_time datetime
select @start_time=getdate()
select * from northwind.dbo.employees
select '查询语句的执行时间(毫秒)'=datediff(ms,@start_time,getdate())
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 6查询成批的sql语句的执行时间:
create table #save_time(start_time datetime not null)
insert #save_time values(getdate())
go
select * from employees
go
select * from orders
go
select '查询语句的执行时间(毫秒)'=datediff(ms,start_time,getdate())
from #save_time
drop table #save_time
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 7返回语句的执行计划内容:
set showplan_all on
go
select * from pubs.dbo.authors
go
set showplan_all off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 8从执行计划判断是否需要优化SQL:
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*SEEK操作*/
set showplan_all on
go
select * from pubs.dbo.sales where stor_id>='7131'
go
set showplan_all off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*SCAN操作*/
set showplan_all on
go
select * from pubs.dbo.sales where ord_date is not null
go
set showplan_all off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 9连接查询VS子查询:
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*子查询*/
set statistics io on
go
select au_fname,au_lname from pubs.dbo.authors where au_id in
(select au_id from pubs.dbo.titleauthor)
set statistics io off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//*连接查询*/
set statistics io on
go
select distinct au_fname,au_lname from pubs.dbo.authors as a inner join
pubs.dbo.titleauthor as t on a.au_id=t.au_id
go
set statistics io off
go
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--Demo 10智能优化:
select p1.productname from northwind.dbo.products as p1 inner join
northwind.dbo.products as p2 on (p1.unitprice=p2.unitprice)
where p2.productname like 'Alice%'
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
1