MSSQL运用

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

角色与权限

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

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
BEGINDECLARE @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