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

数据类别

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

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