SQL Server实用写法汇总

此文更偏向于实施人员，开发人员的推荐看我写的另一篇随笔
1、通用表循环，常用于对一些分表的操作，比如这里有很多张表，表名类似delivery_0、delivery_1...的，就可以用这个来循环所有表来进行操作。原理是利用游标

declare @MyTableName varchar(255);
declare My_Cursor cursor for select TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME like 'delivery\_%' escape '\';
open My_Cursor;
fetch next from My_Cursor into @MyTableName;
while (@@FETCH_STATUS = 0)
begin
    exec('select * from ' + @MyTableName)
    fetch next from My_Cursor into @MyTableName;
end
close My_Cursor;
deallocate My_Cursor

ps 还可以在循环中insert 数据到临时表，然后就可以聚集所有表的数据

 

2、通用表数据循环，常用于遍历表内数据。这个的应用场景比较多，譬如行转列、根据A表数据，插入B表及其子表数据（可以使用这个拿到B表当前插入最新记录的主键值，然后在插入子表数据时可以使用 set @XXId = ident_current('tableName');）

 

Declare 
@row int = 1,  --行记录数
@count int,--总记录数
@XXid bigint; --XXId

IF EXISTS(select 1 from tempdb..sysobjects where id=object_id('tempdb..#Temp'))
BEGIN
    DROP TABLE #Temp
END

SELECT ROW_NUMBER() OVER (ORDER BY t.Id ASC) rowid, tableName.* into #Temp from tableName
set @count = (select COUNT(1) from #Temp)

while @row <= @count
BEGIN
    select @XXid = XXid from #Temp where rowid=@row;
    set @row = @row +1;
END

注1：If为判断语句，begin & end 相当于起止括号
注2：while为循环语句
注3：ident_current('tableName');为获取这个表的主键列的值 

3、exec sp_executesql，当查询语句需要拼接（也就是字符串），或者需要从查询中给某个变量赋值时，使用这个
假设我们有一个Student表（有Id，ClassId，Name 三列）

declare @classId int = 0;
declare @name nvarchar(10);
declare @sql nvarchar(max);
set @sql = N'select @classId = ClassId, @name = Name from Student where Id = 1';
exec sp_executesql @sql, N'@classId int, @name nvarchar(10) output', @classId, @name output;
select @classId, @name;

执行结果如下，可以看到，即便在查询中给@classId赋值但没有写output，外面的@classId也不会改变其值，写了output的@name就成功赋值了。@sql里面的变量实际上是和外部不同步的，类似函数调用时的值传递，加了output就类似C#的ref修饰符（其他语言也有类似的语法吧），所以字符串里面的@classId可以写成@c或者别的，但为了提高可读性，写成了跟外部变量一样的名字

熟练以上3种写法后，再查一查官方文档，基本上就可以把sql当编程了，想怎么输出都可以，下面再讲一些不那么常用的写法
4、定义函数，这里以一个比较实用的分割字符串函数为例（2016版本后用String_Split，但2016版本前没有，还得自定义这个函数）

IF OBJECT_ID('STRING_SPLIT') IS NOT NULL
    DROP FUNCTION STRING_SPLIT
go

create FUNCTION [dbo].[STRING_SPLIT](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
    declare @idx int
    declare @slice varchar(8000)
    select @idx = 1
          if len(@String)<1 or @String is null return
    while @idx!= 0
    begin
        set @idx =charindex(@Delimiter,@String)
        if @idx!=0
            set @slice =left(@String,@idx - 1)
        else
            set @slice = @String
        if(len(@slice)>0)
            insert into @temptable(items)values(@slice)
        set @String =right(@String,len(@String)- @idx)
        if len(@String)= 0 break
    end
return
end
go

 

5、定义存储过程，主要是为了所谓的“原子性”，要么代码都执行，要么代码都不执行
{{先挖坑，以后再填}}

6、获取某表数据字典，可以搭配上面的【通用表循环】使用，所谓的数据字典就是打印出这张表的所有字段的简要信息

select 
col.name as '字段名',
t.name as '类型',
case when col.max_length = -1 then 'Max' else Cast(col.max_length as varchar) end as '长度',
ISNULL(dv.[Default Value],'') as '默认值',
case when col.is_nullable = 1 then '√' else '' end as '允许为空'
from sys.columns col 
join sys.types t on col.system_type_id = t.system_type_id 
join (select SC.NAME AS "Column Name", SM.TEXT AS "Default Value", SO.name as 'Table Name', SC.colid as 'Col Id' from dbo.sysobjects SO JOIN dbo.syscolumns SC ON SO.id = SC.id LEFT JOIN dbo.syscomments SM ON SC.cdefault = SM.id) dv on col.name=dv.[Column Name] and col.object_id = OBJECT_ID(dv.[Table Name])
where t.name <> 'sysname' and col.object_id = OBJECT_ID(N'tableName')
order by dv.[Col Id]

 

7、生成指定长度的随机字符串函数（本想随机插入一些用户名，但残念的是没有汉字）

if object_id('func_random','fn') is not null
  drop function func_random;
go
create function dbo.func_random(@length int)
returns nvarchar(200)
as
begin
  declare @result nvarchar(200);
  declare @i int,
          @random int;

  set @result = '';
  set @i = 0 ;
  while @i < @length
  begin
    select @random = ceiling(random*150) from v_random ; --调整此值产生的范围为0~150，可能概率不同了，随机程度不一样
    if (@random between 48 and 57 ) OR (@random between 65 and 90) OR (@random between 97 and 122) --0~9 A~Z a~z
    begin
      SET @result = @result + nchar(@random)
      set @i= @i + 1
    end
         
  end;

  return (@result);
end
go

/*测试随机字符串函数*/
select dbo.func_random(10);