TSQL使用小结

--1.存储过程和自定义函数都可以实现递归调用，函数定义时，参数需要放在括号内，并且有返回值，函数调用时，需要通过设定变量值或者在查询语句中，参数放在括号内，存储过程则使用exec uspName params...，参数不需要放在括号内。

--函数定义和使用
Create function [dbo].[GetBitNumber]
( 
    @s varchar(4)
)
returns int 
as
begin
    set @s = upper(@s)
    declare @r  int = 0;
    if(CHARINDEX('A',@s)>0)
        set @r+=1;
    if(CHARINDEX('B',@s)>0)
        set @r+=2;
    if(CHARINDEX('C',@s)>0)
        set @r+=4;
    if(CHARINDEX('D',@s)>0)
        set @r+=8;
    return @r
end
go

select dbo.GetBitNumber('ABD')

--或者
declare @result int
set @result = dbo.GetBitNumber('ABD')

--存储过程的定义和使用
Create proc [dbo].[usp_SyncDBSQL]
    @catalog varchar(50)= 'EES_CSLSOUT',                        --数据库名称
    @serverIP varchar(50)='192.168.0.102',        --数据库服务器地址
    @userId varchar(20)='sa',                    --登录用户名
    @password varchar(50)='HX1q2w3e4r'            --登录密码
as
begin
    create table #tmp
    (
        name varchar(200),
        network_name varchar(200),
        status varchar(200),
        id int,
        collation_name varchar(200),
        connect_timeout int,
        query_timeout int
    )

    insert into #tmp
    exec sp_helpserver
    declare @count int = 0
    select @count = COUNT(*) from #tmp where name='syncDBLink'
    if(@count>0)
    begin
        exec sp_dropserver 'syncDBLink','droplogins'   
    end
    drop table #tmp

    exec   sp_addlinkedserver     'syncDBLink','','SQLOLEDB',@serverIP,'','',@catalog   
    exec   sp_addlinkedsrvlogin   'syncDBLink','false',null,@userId,@password   
    exec   sp_serveroption   'syncDBLink', 'rpc out', 'true'

    declare @sql varchar(max) = '
    declare @name varchar(200),@typeKey varchar(20)
    declare cur cursor for 
    select name, case type when ''p'' then ''proc'' else ''function'' end as typeKey
    from sysobjects where type in(''fn'',''tf'',''p'')
    open cur
    fetch next from cur into @name,@typeKey
    while @@FETCH_STATUS=0
    begin
        exec(''drop '' + @typeKey + ''['' + @name + '']'')    
        fetch next from cur into @name,@typeKey
    end
    close cur
    deallocate cur'

    exec(@sql) at syncDBLink

    declare @id int

    declare cur0 cursor for
    select id from sysobjects  where type in ('fn','tf','p') order by crdate
    open cur0
    fetch next from cur0 into @id
    while @@FETCH_STATUS=0
    begin
        set @sql = object_definition(@id);
        exec(@sql) at syncDBLink
        fetch next from cur0 into @id
    end
    close cur0
    deallocate cur0

    exec sp_dropserver 'syncDBLink','droplogins'   
end
go

--调用方式
exec usp_SyncDBSQL 'EESSP1','192.168.0.101','sa','hx123456'

--2.如果在递归调用中，使用了游标，则需要声明为Local,如：

declare cur cursor local for
select id,dirName from AllFolder where parentId = @dirId

--3.递归的CTE有两部分组成，一部分是原查询，然后用union all 关联结合CTE的递归查询。如： 

;with cte as
(
 select DIRNAME,PARENTID,ID,0 as level,DIRNAME+CONVERT(varchar(max),'') as fPath,RESTYPEID from RESDIR where PARENTID=0
 union all 
 select r.DIRNAME,r.PARENTID,r.ID,cte.level + 1,cte.fPath + '/' + r.DIRNAME,r.RESTYPEID from RESDIR r
 inner join cte on r.PARENTID = cte.ID
)
select cte.ID,cte.DIRNAME,m.value + '/' +cte.fPath as fPath,cte.level 
into dm
from cte
inner join MATECONTENTS m on m.ID = cte.RESTYPEID
order by fPath


--4.exec()查询结果可以直接插入表格

Create table Demo_Values
(
PKID int not null identity(1, 1) primary key
,DName Nvarchar(20) null       
,DCode NVarchar(30) null       
,DDate datetime null
)  
go    
--this SQL is only for SQL Server 2008  
Insert into Demo_Values  (DName, DCode, DDate)      
values
 ('DemoA', 'AAA', GETDATE())         
,('DemoB', 'BBB', GETDATE())         
,('DemoC', 'CCC', GETDATE())         
,('DemoD', 'DDD', GETDATE())         
,('DemoE', 'EEE', GETDATE()) 

select * from Demo_Values

insert into Demo_Values(DName, DCode, DDate)     
exec('select  DName, DCode, DDate from Demo_Values')