Sql Server数据库常用Transact-SQL脚本(推荐)

DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID;
GO

视图

1、创建视图

CREATE VIEW V_EmployeeHireDate
AS
SELECT p.FirstName, p.LastName, e.HireDate
FROM HumanResources.Employee AS e JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID ;
GO 

2、删除视图

DROP VIEW V_EmployeeHireDate; 

存储过程

1、创建存储过程

CREATE PROCEDURE P_UspGetEmployeesTest
 @LastName nvarchar(50),
 @FirstName nvarchar(50)
AS
 SELECT FirstName, LastName, Department
 FROM HumanResources.vEmployeeDepartmentHistory
 WHERE FirstName = @FirstName AND LastName = @LastName
 AND EndDate IS NULL;
GO 

2、删除存储过程

DROP PROCEDURE P_UspGetEmployeesTest; 

3、执行存储过程

EXEC P_UspGetEmployeesTest N'Ackerman', N'Pilar';
-- Or
EXEC P_UspGetEmployeesTest @LastName = N'Ackerman', @FirstName = N'Pilar';
GO
-- Or
EXECUTE P_UspGetEmployeesTest @FirstName = N'Pilar', @LastName = N'Ackerman';
GO 

4、重命名存储过程

EXEC sp_rename 'P_UspGetAllEmployeesTest', 'P_UspEveryEmployeeTest2'; 

5、带有输出参数的存储过程

CREATE PROCEDURE P_UspGetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@SalesYTD money OUTPUT
AS
 SELECT @SalesYTD = SalesYTD
 FROM SalesPerson AS sp
 JOIN vEmployee AS e ON e.BusinessEntityID = sp.BusinessEntityID
 WHERE LastName = @SalesPerson;
RETURN
GO
--调用
DECLARE @SalesYTDBySalesPerson money;
EXECUTE P_UspGetEmployeeSalesYTD
 N'Blythe',
 @SalesYTD = @SalesYTDBySalesPerson OUTPUT;
GO 

数据类型

总结

以上所述是小编给大家介绍的Sql Server数据库常用Transact-SQL脚本，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对软件开发网网站的支持！如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！

您可能感兴趣的文章:mysql报错：Deadlock found when trying to get lock; try restarting transaction的解决方法c#中SqlTransaction——事务详解mysql Non-Transactional Database Only(只支持MyISAM)浅谈Transact-SQLMySQL数据库事务隔离级别介绍(Transaction Isolation Level)Transact_SQL 小手册