SQLServer存储过程收集总结

------------------------------------------------------------------------------
建表
declare @table1 table(id int,[name] varchar(20),score int)
insert into @table1 
select 1,'xf',80
union select 2,'bb',81
union select 3,'cc',82
--select * from @table1
------------------------------------------------------------------------------
--case when，if else区别
--case when 能在SQL语句中的任何部分使用，返回结果可以当成SQL语句的一部分
--if else 只能当外部判断，比如变量的增加，判断执行A段代码或者B段代码。而它不能改变A代码执行结果
declare @tt int
--case when使用
set @tt=-1
select *  from @table1 where id=
case 
when @tt=-1 then id
when @tt<>-1 then @tt
end
--if else使用
if @tt=-1
select * from @table1 
else
select * from @table1 where id=@tt
if object_id('tblTempView') is not null
drop view tblTempView
go
-------------------------------------------------------------------------------
创建视图
create view tblTempView(id,ParentId)
as
select id,ParentId from tblTemp where id<5
select * from tblTempView
-------------------------------------------------------------------------------
--存储过程与函数的区别
--本质上没区别。只是函数有如：只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行。执行的本质都一样。存储过程 return 只能返回整型数值，默认0,1返回是否执行成功。函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少。     
1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。      
2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。       
3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。      
4. 当存储过程和被执行时候SQL Manager会到procedure cache中去取相应查询语句如果在procedure cache里没有相应查询语句SQL Manager就会对存储过程和进行编译。
Procedure cache中保存是执行计划(execution plan)当编译好的后就执行procedure cache中execution plan的后SQL SERVER会根据每个execution plan实际情况来考虑是否要在cache中保存这个plan评判标准个是这个execution plan可能被使用频率；其次是生成这个plan代价也就是编译耗时保存在cache中plan在下次执行时就不用再编译了。 
if object_id('fxTest') is not null
drop proc fxTest
go
create procedure dbo.fxTest(@UserId varchar(20), @UserName varchar(30) output
)
as
begin
declare @cnt int
select @cnt=count(*),@UserName=username from users where userid=@UserId group by username
return @cnt
end
--------------------------------------------------------------------------------
--建立执行存储过程
if object_id('fxaaa') is not null
drop proc fxaaa
go
create procedure fxaaa(@para1 int, @para2 varchar(20), @para3 varchar(20) output)
as
begin
declare @tmp varchar(20)
set         @tmp='test'
select    @para3=@tmp
end
-------------------------------------------------------------------------------
函数返回table
IF OBJECT_ID (N'rTable') IS NOT NULL
DROP FUNCTION rTable
go
create function rTable(   )
returns @table table (id int,[name] nvarchar(50))
as
begin
insert into @table(id,[name])values(1,'aa')
insert into @table(id,[name])values(2,'bb')
return
end
go
select * from rtable()
-------------------------------------------------------------------------------
函数返回一变量
if object_id('fxTestFun') is not null
drop function fxTestFun
go
create function fxTestFun( @userid varchar(20) )
returns varchar(30)
as
begin
declare @name varchar(30)
select    @name=username from users where userid=@userid
return    @name
end
go
--------------------------------------------------------------------------
函数返回一记录集
if object_id('fxTestFun2') is not null
drop function fxTestFun2
go
create function fxTestFun2(@userid varchar(20))
returns @tmpTable table
(userid varchar(20) collate Chinese_PRC_CI_AI_WS not null , userName varchar(30))
as
begin
insert into @tmpTable(userid,username)
select userid,username from users where userid=@UserId
return
end
go
----------------------------------------------------------------------------
临时表的创建
declare @table1 table(id int,[name] varchar(20),score int)
insert into @table1 
select 1,'xf',80 union select 2,'bb',90
select * from @table1
----------------------------------------------------------------------------
游标的使用
IF OBJECT_ID (N'getScoreStr') IS NOT NULL
DROP FUNCTION getScoreStr
go
create function getScoreStr(@name as nvarchar(50))
returns nvarchar(100)
as
begin
declare @outputCursor cursor
set @outputCursor = cursor fast_forward for
 select score from Course where [name]=@name
open @outputCursor
declare @score int
declare @rStr as nvarchar(200)
declare @rint as int
set @rStr=''
set @rint=0
fetch from @outputCursor into  @score
while @@fetch_status=0
begin
set @rStr=@rStr + convert(nvarchar(5),@score)+','
set @rint=@rint + @score
fetch next from @outputCursor into @score
end
--select @rStr
--select @rint
return  @rStr
end
go
select dbo.getScoreStr('a1')
---------------------------------------------------------------------------------
执行存储过程函数
declare @userId varchar(20)
declare @UserName varchar(30)
declare @cnt int
set @userId='K2547'
execute  @cnt=fxTest @userId,@UserName output
select @cnt, @UserName
--execute @UserName=fxTestFun @userId
--select @UserName
select dbo.fxTestFun(@userId)
select * from dbo.fxTestFun2(@userId)
--select * from dbo.users
--特殊情况
select * from dbo.fxTestFun2(@userId) a join dbo.users b on a.userid=b.userid collate Chinese_PRC_CI_AI_WS   
--select * from users where userid in(select userid from dbo.fxTestFun2(@userId)) 
-----------------------------------------------------------------------------------
动态创建函数
declare @del varchar(200)
set @del='IF OBJECT_ID (N''rTable'') IS NOT NULL DROP FUNCTION rTable'
declare @s varchar(8000)
set  @s='select tableMainId '
select  @s=@s+',
['+ fieldCode+']=
case when fieldCode='''+fieldCode+''' then fieldValue else '''' end'
from tableMain_AddField 
group by fieldCode
set @s=@s+' from tableMain_AddField '
declare @parStr varchar(1000)
set @parStr=''
select  @parStr=@parStr+ fieldCode+' nvarchar(50),'
from tableMain_AddField 
group by fieldCode
set @parStr= substring(@parStr,1, len(@parStr)-1)
declare @sFun varchar(8000)
set @sFun=' 
create function rTable() returns
@table table (tableMainId int,'
set @sFun=@sFun+@parStr +') 
as begin
insert into @table '
set @sFun=@sFun+@s
set @sFun=@sFun+' return end'
exec(@del)
exec(@sFun)
select * from rtable()
-------------------------------------------------------------------------------------
create function rTable(@id int) 
returns
 @table table (tableMainId int,field3 nvarchar(50),field4 nvarchar(50),field5 nvarchar(50),field6 nvarchar(50),field7 nvarchar(50)) 
as begin
declare @str varchar(1000)
set @str= dbtest.dbo.getInsertSql(@id)

   insert into @table exec(@str) 
return 
end
--------------------------------------------------------------------------------------
动态创建函数的存储过程
if OBJECT_ID('makeNewFun') is not null
drop PROCEDURE   makeNewFun
go
CREATE PROCEDURE makeNewFun
as
begin
declare @del varchar(200)
set @del='IF OBJECT_ID (N''rTable'') IS NOT NULL DROP FUNCTION rTable'
declare @s varchar(8000)
set  @s='select tableMainId '
select  @s=@s+',
['+ fieldCode+']=
case when fieldCode='''+fieldCode+''' then fieldValue else '''' end'
from tableMain_AddField 
group by fieldCode
set @s=@s+' from tableMain_AddField where tableMainID= case when @id<>-1 then @id else tableMainID end'
--print @s
declare @parStr varchar(1000)
set @parStr=''
select  @parStr=@parStr+ fieldCode+' nvarchar(50),'
from tableMain_AddField 
group by fieldCode
set @parStr= substring(@parStr,1, len(@parStr)-1)
--print @parStr
declare @sFun varchar(8000)
set @sFun=' 
create function rTable(@id int) returns
@table table (tableMainId int,'
set @sFun=@sFun+@parStr +') 
as begin
insert into @table '
set @sFun=@sFun+@s
set @sFun=@sFun+' return end'
--print @sFun
exec(@del)
exec(@sFun)
end
-------------------------------------------------------------------------------------
执行动态创建函数的存储过程
exec makeNewFun 
select * from tableMain a join rtable() b on a.id=b.tableMainid
select * from rtable()
-------------------------------------------------------------------------------------
动态行变成动态列
Create table  T  (Class varchar(2),CallDate datetime, CallCount int)
insert into T select '1','2005-8-8',40
union all select '1','2005-8-7',6
union all select '2','2005-8-8',77
union all select '3','2005-8-9',33
union all select '3','2005-8-8',9
union all select '3','2005-8-7',21
--动态SQL
declare @s varchar(8000)
set @s='select CallDate '
select @s=@s+',[CallCount'+Class+']=sum(case when Class='''+Class+''' then CallCount else 0 end)'
from T 
group by Class
set @s=@s+' from T group by CallDate order by CallDate desc '
exec(@s)
--------------------------------------------------------------------------------------