MSSQL记录

批量添加：

DECLARE @GID INT,@UID INT,@Indexs INT
SET @GID=1
SET @UID=37
SET @Indexs=0
WHILE @GID<674 --674要执行插入的次数
BEGIN
INSERT INTO TB_Employee (CompetitionId, UserId,GroupId,GroupSouce,IsAudit,IsFinal ) -- CompetitionUser 为表名
VALUES ( 52, @UID, @GID, 1, 1, 0)
SET @Indexs = @Indexs+1;
SET @UID = @UID+1;
IF(@Indexs=3)
BEGIN
SET @Indexs=0
SET @GID=@GID+1
END
END

条件排序：

select * FROM TB_Employee ORDER BY
case
when Salary = 1200 then 1
when Salary > 1200 then 2
when Salary < 1200 then 3
end,EmployeeId;

select * FROM TB_Employee ORDER BY
case
when Salary in (1200,238) then 1
else 2
end,EmployeeId

存储过程：

USE [People]
GO

create proc Proc_GetIndexNum
@collegeId int,
@userId int,
@temp1 int output,
@temp2 int output,
@temp3 int output,
@temp4 int output

as
-- 数量1
select @temp1=COUNT(1) from TB_Employee a where IsRelease=1 and IsDelete=0 and CollegeId =@collegeId
and ((a.Type=1 and a.State<102) or (a.Type=2 and a.State<202) or (a.Type=3 and a.State<302))
and (select count(1) from TB_Teacher where CompetitionId=a.Id and UserId=@userId) < 1

-- 数量2
select @temp2=COUNT(1) from TB_Employee a where IsRelease=1 and IsDelete=0 and CollegeId =@collegeId
and ((a.Type=1 and a.State=103) or (a.Type=2 and a.State=203) or (a.Type=3 and (a.State=303 or a.State=306)))
and (select count(1) from TB_Teacher where CompetitionId=a.Id and UserId=@userId and IsAudit=1)>0

--数量3
select @temp3=COUNT(1) from TB_Employee where IsRelease=2 and IsDelete=0 and CollegeId =@collegeId

-- 数量4
select @temp4=COUNT(*) from Account a inner join UserInfo b
on a.UserId = b.Id and b.Status=2 and b.CollegeId=@collegeId

-- 执行测试

--SET STATISTICS TIME ON

--DECLARE @CID INT,@UID INT,@NumA INT,@NumB INT,@NumC INT,@NumD INT
--set @CID=1
--set @UID=1
--exec Proc_GetIndexNum @CID,@UID,@NumA out,@NumB out,@NumC out,@NumD out
--SET STATISTICS TIME OFF

 

更改原字段内容：

update UserInfo set LoginName=(LoginName+'(已删除)') where LoginName=@LoginName

 

 

[通过sql在目标电脑创建文件路径]：

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

GO

ExEc xp_cmdshell 'mkdir E:\Aroject' --调用DOS命令创建project文件夹

 

[获取目标电脑数据库文件存放位置，并在此创建数据库]：

declare  @path  varchar(200) 

select  @path  =  filename  from  master.dbo.sysfiles 

set  @path  =  ltrim(REVERSE(@path)) 

set  @path  =  REVERSE(substring(@path,CHARINDEX('\',@path),len(@path))) 

exec('CREATE DATABASE xxxx ON PRIMARY (NAME = "xxxx", FILENAME = "' + @path + 'xxxx.mdf", SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) LOG ON (NAME = "xxxx_log", FILENAME = "' + @path + 'xxxx.ldf", SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)')

 

[如果数据库已存在，关闭数据库连接，并删除数据库]：

declare @i int declare cur cursor for select spid from sysprocesses where db_name(dbid)= 'DB_Test' open cur fetch next from cur into @i while @@fetch_status=0 begin exec('kill '+@i) fetch next from cur into @i end close cur deallocate cur

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'DB_Test')

DROP DATABASE [DB_Test]

 

---------------------------------------------------
-- desc: 通用分页存储过程
---------------------------------------------------

create PROCEDURE [dbo].[Proc_CommonPagingStoredProcedure]
@Tables nvarchar(1000), --表名,多表请使用 tableA a inner join tableB b On a.AID = b.AID
@PK nvarchar(100), --主键，可以带表头 a.AID
@Sort nvarchar(200) = '', --排序字段
@PageNumber int = 1, --开始页码
@PageSize int = 10, --页大小
@Fields nvarchar(1000) = '*', --读取字段
@Filter nvarchar(1000) = NULL, --Where条件
@isCount bit = 0 , --1 --是否获得总记录数
@Total int output
AS

DECLARE @strFilter nvarchar(2000)
declare @sql Nvarchar(max)
IF @Filter IS NOT NULL AND @Filter != ''
BEGIN
SET @strFilter = ' WHERE 1=1 ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ' '
END
if @isCount = 1 --获得记录条数
begin
Declare @CountSql Nvarchar(max)
Set @CountSql = 'SELECT @TotalCount= Count(1) FROM ' + @Tables + @strFilter
Execute sp_executesql @CountSql,N'@TotalCount int output',@TotalCount= @Total Output
end

if @Sort is null or @Sort = ''''
set @Sort = @PK + ' DESC '

IF @PageNumber < 1
SET @PageNumber = 1

if @PageNumber = 1 --第一页提高性能
begin
set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ ' from ' + @Tables + ' ' + @strFilter + ' ORDER BY '+ @Sort
end
else
begin
DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)

SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
set @sql = ' SELECT * '+
'FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum,
'+@Fields+ '
FROM '+@Tables+ @strFilter +' ) AS D
Where rownum >= '+@START_ID+' AND rownum <=' +@END_ID +' ORDER BY '+substring(@Sort,charindex('.',@Sort)+1,len(@Sort)-charindex('.',@Sort))
END

EXEC(@sql)

 

【为字段添加唯一约束】：

alter table [GTA_FPBT_Training_V1.5].dbo.AssessmentResults
add constraint [user_match_id] unique (UserId,CompetitionId,TrainExamId)