聽說幸福…在山的那邊 (浪機子)

USE [TempDB]
GO
/****** Object:  StoredProcedure [dbo].[JOB_ExpireMail_Manual]    Script Date: 2018/1/30 上午 10:01:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

TempUser 結構描述 'dbo'，資料庫 'msdb'，物件 'sp_send_dbmail' 沒有 EXECUTE 權限
EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole',@membername = 'TempUser ';

SQL Store Procedure 的寫法**************
USE [TempDB]
GO
/****** Object:  StoredProcedure [dbo].[JOB_GVReport]    Script Date: 2018/1/30 上午 09:49:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

FUNCTION 也可以回傳 TABLE 資料哦
USE [TempDB]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_EmpIDOrgan]    Script Date: 2018/1/30 上午 09:44:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

 
-- =============================================
-- Author: 
-- Create date: 20161101
-- Description: 取得逾期天數
-- select [dbo].[fn_GetExpireDat](CaseNo),ApplyDat,* from Tbl_Table
-- =============================================
ALTER FUNCTION [dbo].[fn_GetExpireDat]
( 
  @CaseNo varchar(48)
)
RETURNS  int
AS
BEGIN
    declare @ExpireDat int
 set @ExpireDat = 0
 select @ExpireDat = datediff(day,Convert(CHAR(8),Dateadd(D,cast(Para3.ParaDefine as int),ApplyDat),112) , Convert(CHAR(8),getdate(),112) )
 FROM  Tbl_Table
 INNER JOIN Sys_Parameter Para3 on Para3.ParaType = 'Public' and  Para3.ParaKind ='Priority'  and Para3.ParaValue  = Tbl_CaseMain.Priority
 WHERE CaseNo =@CaseNo and left(isnull(CaseCurrFlowStepID,''),1) <> 'Z'

USE [TEMPDB]
GO
/****** Object:  UserDefinedFunction [dbo].[SplitStr]    Script Date: 2017/7/28 下午 04:32:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [TEMPDB]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_FUZZY]    Script Date: 2017/7/28 下午 04:29:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [TEMPDB]
GO
/****** Object:  UserDefinedFunction [dbo].[FindStr]    Script Date: 2017/7/28 下午 04:24:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [TESTDB]
GO
/****** Object:  UserDefinedFunction [dbo].[f_Convert]    Script Date: 2017/7/28 下午 04:21:04 ******/
SET ANSI_NULLS ON
GO

  ##使用在組織的判斷上，非常的方便##
  例如，組織檔的裡有上一階層的組織，如何算出某組織下有多少個單位