MSSQL运用

做过的事情，隔几年又再做相同的事情，做一下记录。

unique key

ALTER TABLE [dbo].[tbl_abc]
ADD UNIQUE (abc_id);

角色与权限

• 创建账号与设定执行存储过程权限

Use testDB
CREATE LOGIN acct WITH PASSWORD='p1'
CREATE USER acct FOR LOGIN acct
GO 
GRANT EXECUTE ON SP_Test TO acct;
GO 

存储过程

调用写好的SQL语句

USE [TestDB]
GO

/****** Object:  StoredProcedure [dbo].[Search]    Script Date: 21/2/2024 15:39:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Search]
@FirstName VARCHAR (50)
AS
BEGIN
    DECLARE @SQL AS NVARCHAR (MAX);
    SET @SQL = 'SELECT * FROM table';
    EXECUTE sp_executesql @SQL;
END
GO

数据类别

数据类型	转string
bit	“Convert(varchar(1),@” + FieldName + “)”
int
date	“Convert(varchar(8), @”+FieldName+“, 112)”
long
float	“CONVERT(varchar(50), @”+FieldName+“, 128)”
bigint	“Convert(varchar,@” + FieldName + “)”
decimal
smallint
datetime2	“Convert(varchar(23), @” + FieldName + “, 127)”
uniqueidentifier	“convert(nvarchar(36),@” + FieldName + “)”

编号字段重命名并自动生成

ALTER TABLE name ADD id_NEW [bigint] IDENTITY(1,1)
GO
ALTER TABLE name DROP COLUMN id
GO

合拼搜索结果

declare @iList nvarchar(Max)
select @iList=coalesce(@iList +','+
Convert(nvarchar,id ),Convert(nvarchar,id )
) FROM tbl_A
group by (id)
print @iList

拼接字段

select *
from tbl_A A
where  ((select CONCAT(A.a1_zh,' ',A.a2_zh , ' ' ,A.a3_zh))   like N'%A%')

Copy Content of Table

insert into DB1.dbo.tblA ([name],[name_zh]) 
select [name],[name_zh] from DB2.dbo.tblA;