需要帮助将其变成一个功能 [英] 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 $这样的东西bSELECT * 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 haveSELECT ... 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屋!