SQL Server创建表，存储过程，function函数脚本规范

--创建新表

/****************************************************************************************

*****************************************************************************************
* File Name : Rollout_Priming_dbo.Test
* Description/Purpose : Priming to 
* Last Author : Huairong Chen
* Last Check Date : 08/31/2020
*
* Returns :
*
*****************************************************************************************
****************************************************************************************/

USE [Finance]
GO

IF NOT EXISTS (SELECT 1 FROM sysObjects WHERE Id=Object_ID(N'[dbo].[Test]') AND xtype='U')
BEGIN
CREATE TABLE [dbo].[Test](
[UserId] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[UserName] Varchar(20) NOT NULL,
[EnabledFlag] Bit NOT NULL,
[CreatedBy] Varchar(50) NOT NULL,
[CreatedDate] Datetime,
[ModifiedBy] Varchar(50) NOT NULL,
[ModifiedDate] Datetime,
[RowVer] Rowversion
)

END
GO

 

 

--创建存储过程

/****************************************************************************************

*****************************************************************************************
* File Name : Rollout_GetReasonableCauseCode_sp.sql
* Description/Purpose : 

* Last Author : 
* Last Check Date : 09/15/2020
*
* Returns :
*
* Assumptions :
*****************************************************************************************
****************************************************************************************/

Use Finance
Go

IF OBJECT_ID('[dbo].[GetReasonableCauseCode_sp]') IS NOT NULL
DROP PROC [dbo].[GetReasonableCauseCode_sp]
GO

CREATE PROCEDURE [dbo].[GetReasonableCauseCode_sp]
AS
BEGIN

 BEGIN TRY

　　xxxx

END TRY

BEGIN CATCH
　　throw
END CATCH
END
GO
GRANT EXECUTE
ON [dbo].[GetReasonableCauseCode_sp]
TO FinanceRole

 

 

--创建function函数

USE [fbrsql]
GO

IF EXISTS (
SELECT 1
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetDisbursementCodeByPmiDetail]')
AND type IN (

N'FN'
,N'IF'
,N'TF'
,N'FS'
,N'FT'

)
)
DROP FUNCTION [dbo].[fn_GetDisbursementCodeByPmiDetail]
GO

CREATE FUNCTION [dbo].[fn_GetDisbursementCodeByPmiDetail] (@PMIDTLID INT)
RETURNS VARCHAR(3)
AS
BEGIN

xxx

END
GO