Sqlserver小技巧

1.获取异常信息

select ERROR_NUMBER(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_PROCEDURE(), ERROR_LINE() ,ERROR_MESSAGE()

2.查看视图创建的时间：

Code

select [name],create_date,modify_date
FROM
sys.all_objects
where name = 'VW_F_Emp_RedeployInfoWF'

3.查看存储过程创建的时间：

Code

select [name],create_date,modify_date
FROM
sys.all_objects
where 
type_desc = N'SQL_STORED_PROCEDURE' and
name = 'PR_CheckDeptID'

4.SQL In 语句如何返回表中不存在的值

假设表中内容如下：

ID	ProjectName
1	张
2	王

执行 select * from [Table-Test] where ID in (1,3)
因为3不存在
所以sql会返回如下结果：

ID	ProjectName
1	张

如何改写sql，让表中不存在的3也返回结果呢？
结果如下：

ID	ProjectName
1	张
2	王
3	Null

实现：

Code

CREATE function Get_StrArrayLength
(
 @str varchar(1024),  --要分割的字符串
 @split varchar(10)  --分隔符号
)
returns int
as
 begin
  declare @location int
  declare @start int
  declare @length int
  set @str=ltrim(rtrim(@str))
  set @location=charindex(@split,@str)
  set @length=1
   while @location<>0
     begin
      set @start=@location+1
      set @location=charindex(@split,@str,@start)
      set @length=@length+1
     end
   return @length
 end

CREATE function Get_StrArrayStrOfIndex
(
 @str varchar(1024),  --要分割的字符串
 @split varchar(10),  --分隔符号
 @index int --取第几个元素
)
returns varchar(1024)
as
begin
 declare @location int
 declare @start int
 declare @next int
 declare @seed int
 set @str=ltrim(rtrim(@str))
 set @start=1
 set @next=1
 set @seed=len(@split)
 set @location=charindex(@split,@str)
 while @location<>0 and @index>@next
   begin
    set @start=@location+@seed
    set @location=charindex(@split,@str,@start)
    set @next=@next+1
   end
 if @location =0 select @location =len(@str)+1
 return substring(@str,@start,@location-@start)
end

declare @canshu int
declare @str varchar(50)
--定义字符串变量
set @str='1,3,5,6,7'
declare @next int  
set @next=1
Declare  @Test table
(
	ID int,
	ProjectName varchar(50)
)
insert into @Test select * from [Table-Test]

while @next<=dbo.Get_StrArrayLength(@str,',')
  begin
     set @canshu = dbo.Get_StrArrayStrOfIndex(@str,',',@next)
	 if not exists(select * from @Test where ID =@canshu)
		begin
		 Insert Into @Test(ID,ProjectName) values(@canshu,null)
		end
     set @next=@next+1
  end
select * from @Test