T-SQL使用小结
2013-01-07 07:57 Andy Ge 阅读(344) 评论(0) 编辑 收藏 举报--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')