需要帮助将其变成一个功能 [英] Need help turning this into a function

问题描述

这是我的尝试，并被要求变成一个功能，但我已经在线查找，找不到任何有助于我这样做的人可以帮助我吗？

  DECLARE   @ StartDate   DATE  = '  01/04/2013' 
  DECLARE   @ EndDate   DATE  = '  30/04/2013' 
 
  SELECT  ContractID 
，LineNumber 
，ContractDate 
，[日期] 
，o.Name 
，u 。[文件] 
，DateAdded 
，DateRemoved 
，u.CTPS 
，B.Name [Branch Name] 
 
  INTO  [TPS] 
  FROM  [合约] c 
  INNER   JOIN  [Account] a 
  ON  [c]。 [AccountFK] = [a]。[AccountID] 
  INNER  加入 [个人资料] p 
  ON  [p]。[ProfileID] = [a]。[ProfileFK] 
  INNER   JOIN  [Line] l 
  ON  [l]。[ContractFK] = [ContractID ] 
  LEFT   JOIN  [Deal] d 
  ON  [c]。[ContractID] = [d]。[ContractFK] 
  INNER   JOIN （ SELECT  BranchTypeFK，BranchID，名称
  FROM  [Branch] b 
）b 
  ON  [d]。[BranchFK] = [b]。[BranchID] 
  AND  BranchTypeFK  IN （ 1  ， 2 ， 4 ）
  INNER   JOIN  [OrganisationType] o 
  ON  [p]。[OrganisationTypeFK] = [o ]。[OrganisationTypeID] 
  LEFT   JOIN （
  SELECT  [CLI]，[日期] [lDate]，[日期 ]，[CTPS]，[文件] = ' 查找' 
  FROM  [查询] tl 
）u 
  ON  u.CLI = l.LineNumber 
  LEFT   JOIN （
  SELECT  [CLI]，[DateAdded]，[DateRemoved]，[CTPS]，[文件] = ' 历史记录' 
  FROM 历史记录h 
）h 
  ON  h.CLI = l.LineNumber 
  WHERE  ContractDate  BETWEEN   @ StartDate   AND   @ EndDate  
 
  SELECT  * 
，[OnTPS] =  CASE  
  WHEN （[日期]  IS   NOT   NULL   AND  [ContractDate]< [日期]）那么 ' 否' 
  WHEN （[日期 ]  IS   NOT   NULL   AND  [ContractDate]> [日期]）那么 < span class =code-string>' 是' 
 
  WHEN （[日期]  IS   NULL   AND  [ContractDate]  BETWEEN  [DateAdded]  AND  [DateRemoved]）那么 ' 是' 
  WHEN （[日期]  IS   NULL   AND  [ContractDate]  NOT   BETWEEN  [DateAdded]  AND  [DateRemoved]） THEN  '  No' 
 
  WHEN （[日期]  IS   NULL   AND  [ContractDate]  BETWEEN  [DateAdded]  AND  [DateRemoved]  AND  [Name] = '  PLC' < span class =code-keyword> OR  [Name] = '  LTD' < span class =code-keyword> AND  CTPS =  1 ）那么 ' 是' 
  WHEN （[< span class =code-keyword>日期]  IS   NULL   AND  [ContractDate]  BETWEEN  [DateAdded]  AND  [DateRemoved] < span class =code-keyword> AND  [Name] = '  PLC' < span class =code-keyword> OR  [Name] = '  LTD' < span class =code-keyword> AND  CTPS =  0 ）那么 ' 否' 
 
  WHEN （[日期]  IS   NOT   NULL   AND  [ContractDate]> [日期]  AND  [名称] = '  PLC'  OR  [Name] = '  LTD'  AND  CTPS =  1 ） 那么 ' 是' 
  WHEN （[日期]  IS   NOT   NULL   AND  [ContractDate]> [< span class =code-keyword> Date ]  AND  [Name] = ' < span class =code-string> PLC'  OR  [Name] = ' < span class = code-string> LTD'  AND  CTPS =  0 ）那么 ' 否' 
  END  
  FROM  [Temp]。[dbo]。[TPS] t 

解决方案

通过在同一主题上发布新问题让我感到困惑！ SQL函数检查cdate是否在tdate之前或之后 [ ^ ]



MSDN创建函数文档 [ ^ ]



相当深入的论文功能 [ ^ ]



最可能用作哟你似乎想要返回很多数据，这个代码项目的文章是使用SQL Server中的表值函数 [ ^ ]





您需要编写脚本来创建一个函数，如下所示（更改名称）

我已经对你想要的东西做了一些假设

 CREATE FUNCTION MyFunction（@StartDate DATE，@ EndDate DATE）
 RETURNS @MyResults TABLE（
 ContractID int NOT NULL，
 LineNumber int NOT NULL，
 ContractDate date NULL ，
 [Date] date NULL，
 o.Name varchar（100）NULL，
 [File] varchar（500）NULL，
 DateAdded date NULL，
 DateRemoved date NULL，
 CTPS varchar（100）NULL，
 [分支名称] varchar（100）NULL，
 YesNO varchar（3）NOT NULL 
）
 AS 
 BEGIN 
  - 删除DECLARE @StartDate DATE = '01 / 04/2013'
  - 删除DECLARE @EndDate DATE = '30 / 04/2013'
 
 / *将此处的其余代码插入此处
 
 SELECT * 
，[OnTPS] = CASE 
 WHEN（[Date] IS NOT NULL且[ContractDate]< [日期]）那么'否'
 WHEN（[日期]不是空和[ContractDate]> [Date]）那么'是'
 
 * / 
 
 INSERT INTO @MyResults 
 
 / *现在从
中插入剩余的代码SELECT * 
，[OnTPS] = CASE 
 WHEN（[Date] IS NOT NULL和[ContractDate]< [Date]）那么'否'
 WHEN（[日期]不为空且[ContractDate]> [Date]）那么'是'
 
 ......等等
 * / 

然后最后把

返回; 
 END; 

您可能需要根据自己的要求整理NULL / NOT NULL声明，而我目前无法对此语法错误进行测试。



运行脚本来创建函数。



注意 - 在你的sql中你有 SELECT ... INTO [TPS] 但稍后你使用

FROM [Temp]。[dbo]。 [TPS ] t - 这些不一定是同一张桌子。我建议明确使用Temp.dbo.TPS或#TMP以避免任何问题。



最后调用函数使用像
$ b $这样的东西b

 SELECT * FROM MyFunction（Date1，Date2）; 

致谢Mika Wendelius的文章在SQL Server中使用表值函数 [ ^ ]从中大量复制此解决方案。

http://msdn.microsoft.com/en-us/library/ms186755。 aspx [ ^ ]

This is my attempt and have been asked to turn into a function but i have looked online and can not find anything that helps me do this can anyone help please?

DECLARE @StartDate DATE = '01/04/2013'
DECLARE @EndDate DATE = '30/04/2013'

SELECT  ContractID
        ,LineNumber
        ,ContractDate
        ,[Date]
        ,o.Name
        ,u.[File]
        ,DateAdded
        ,DateRemoved
        ,u.CTPS
        ,B.Name [Branch Name]

INTO [TPS]
FROM [Contract] c
INNER JOIN [Account] a
    ON [c].[AccountFK] = [a].[AccountID]
INNER JOIN [Profile] p
    ON [p].[ProfileID] = [a].[ProfileFK]
INNER JOIN [Line] l
    ON [l].[ContractFK] = [ContractID]
LEFT JOIN [Deal] d
    ON [c].[ContractID] = [d].[ContractFK]
INNER JOIN  (   SELECT BranchTypeFK, BranchID, Name   
                FROM [Branch] b
            )b
    ON [d].[BranchFK] = [b].[BranchID]
    AND BranchTypeFK IN (1,2,4)
INNER JOIN [OrganisationType] o
    ON [p].[OrganisationTypeFK]  = [o].[OrganisationTypeID] 
LEFT JOIN   (
            SELECT [CLI], [Date] [lDate], [Date], [CTPS], [File] = 'Lookup'  
            FROM [Lookup] tl
            )u
    ON u.CLI  = l.LineNumber
LEFT JOIN   (
            SELECT [CLI], [DateAdded], [DateRemoved], [CTPS], [File] = 'History'  
            FROM History h
            )h
    ON h.CLI  = l.LineNumber 
WHERE ContractDate BETWEEN @StartDate AND @EndDate

SELECT  *
        ,[OnTPS] =  CASE
                    WHEN ([Date] IS NOT NULL AND [ContractDate] < [Date]) THEN 'No'
                    WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date]) THEN 'Yes' 

                    WHEN ([Date] IS NULL AND [ContractDate] BETWEEN [DateAdded] AND [DateRemoved]) THEN 'Yes'
                    WHEN ([Date] IS NULL AND [ContractDate] NOT BETWEEN [DateAdded] AND [DateRemoved]) THEN 'No'

                    WHEN ([Date] IS NULL AND [ContractDate] BETWEEN [DateAdded] AND [DateRemoved] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 1) THEN 'Yes'
                    WHEN ([Date] IS NULL AND [ContractDate] BETWEEN [DateAdded] AND [DateRemoved] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 0) THEN 'No'

                    WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 1) THEN 'Yes'
                    WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date] AND [Name] = 'PLC' OR [Name] = 'LTD' AND CTPS = 0) THEN 'No'
                    END
FROM [Temp].[dbo].[TPS] t

解决方案

You're confusing me by posting new questions on the same topic! SQL Function that checks if a cdate is before or after a tdate[^]

MSDN Create Function Documentation[^] [Edit - just spotted this one in Solution 1]

Fairly indepth treatise on functions[^]

And the one possibly of most use as you seem to want to return a lot of data would be this codeproject article Using Table-Valued Functions in SQL Server[^]

[EDIT - translating the link that shows how to do this...]
You need to write the script to create a function as follows (change the names appropriately)
I've made some assumptions about what you want returned

CREATE FUNCTION MyFunction(@StartDate DATE, @EndDate DATE)
RETURNS @MyResults TABLE (
	ContractID int NOT NULL,
 	LineNumber int NOT NULL,
	ContractDate date NULL,
	[Date] date NULL,
        o.Name varchar(100) NULL,
	[File] varchar(500) NULL,
 	DateAdded date NULL,
	DateRemoved date NULL,
	CTPS varchar(100) NULL,
	[Branch Name] varchar(100) NULL,
        YesNO varchar(3) NOT NULL
) 
AS
BEGIN
	-- remove DECLARE @StartDate DATE = '01/04/2013'
	-- remove DECLARE @EndDate DATE = '30/04/2013'

/* insert the rest of your code here up to 

SELECT *
 ,[OnTPS] = CASE
 WHEN ([Date] IS NOT NULL AND [ContractDate] < [Date]) THEN 'No'
 WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date]) THEN 'Yes' 

*/

INSERT INTO @MyResults

/* Now insert the rest of your code from 
SELECT *
 ,[OnTPS] = CASE
 WHEN ([Date] IS NOT NULL AND [ContractDate] < [Date]) THEN 'No'
 WHEN ([Date] IS NOT NULL AND [ContractDate] > [Date]) THEN 'Yes' 

... etc 
*/

Then at the end put

RETURN;
END;

You may need to tidy up the NULL / NOT NULL declarations according to your own requirements and I'm not able to test this for syntax errors at the moment.

Run the script to create the function.

NOTE - in your sql you have SELECT ... INTO [TPS] but later you use
FROM [Temp].[dbo].[TPS] t - these are not necessarily the same table. I would suggest explicitly using Temp.dbo.TPS OR #TMP to avoid any issues there.

Finally to call the function use something like

SELECT * FROM MyFunction(Date1, Date2);

Acknowledgement to Mika Wendelius for his article Using Table-Valued Functions in SQL Server[^] from which this solution is largely copied.

http://msdn.microsoft.com/en-us/library/ms186755.aspx[^]

这篇关于需要帮助将其变成一个功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！