如何创建一个将接受参数并包含循环的函数

Question

我想创建一个函数，它将使用四个参数(latitudeA、longitudeA，latitudeB、longitude B(。计算一条从a点到B点的直线，分成50个部分。

到目前为止，下面的代码是我想到的最好的东西，但我不能将参数传递到@lata、@latb、@longa、@longb，它们必须是硬编码的。

有人能告诉我如何将下面的代码更改为一个函数，该函数将接受四个参数并给我相同的输出吗？

Set nocount ON
Declare @testa2b Table(point int, lata float, latb float, latitude float, longa float, longb float, longitude float)
Declare @point int = 1
Declare @lata float = 51.1537
Declare @latb float = 34.8723
Declare @latitude float = @lata
Declare @longa float = -0.1821
Declare @longb float = 33.6204
Declare @longitude float = @longa
While @point <= 50
Begin
Insert Into @testa2b Values (@point, @lata, @latb, @latitude, @longa, @longb, @longitude)
Set @point = @point+1
Set @latitude = @lata-(((@lata-@latb)/49)*(@point-1))
Set @longitude = @longa-(((@longa-@longb)/49)*(@point-1))
End
Select point, lata, latb, latitude, longa, longb, longitude
From @testa2b

结果示例

Answer 1

如果你这样做，你可以完全避免WHILE。

DECLARE 
@max_results int = 50,
@lata float = 51.1537,
@latb float = 34.8723,
@longa float = -0.1821,
@longb float = 33.6204;
SELECT
point,
@lata AS lata,
@latb AS latb,
@lata - ( ( ( @lata - @latb ) / 49 ) * ( point - 1 ) ) AS latitude,
@longa AS longa,
@longb AS longb,
@longa - ( ( ( @longa - @longb ) / 49 ) * ( point - 1 ) ) AS longitude
FROM (
SELECT 
ROW_NUMBER() OVER ( ORDER BY [value] ) AS point 
FROM STRING_SPLIT( REPLICATE( ',', @max_results - 1 ), ',' )
) AS n;

返回

+-------+---------+---------+------------------+---------+---------+------------------+
| point |  lata   |  latb   |     latitude     |  longa  |  longb  |    longitude     |
+-------+---------+---------+------------------+---------+---------+------------------+
|     1 | 51.1537 | 34.8723 | 51.1537          | -0.1821 | 33.6204 | -0.1821          |
|     2 | 51.1537 | 34.8723 | 50.8214265306122 | -0.1821 | 33.6204 | 0.50774693877551 |
|     3 | 51.1537 | 34.8723 | 50.4891530612245 | -0.1821 | 33.6204 | 1.19759387755102 |
|     4 | 51.1537 | 34.8723 | 50.1568795918367 | -0.1821 | 33.6204 | 1.88744081632653 |
|     5 | 51.1537 | 34.8723 | 49.824606122449  | -0.1821 | 33.6204 | 2.57728775510204 |
|     6 | 51.1537 | 34.8723 | 49.4923326530612 | -0.1821 | 33.6204 | 3.26713469387755 |
|     7 | 51.1537 | 34.8723 | 49.1600591836735 | -0.1821 | 33.6204 | 3.95698163265306 |
|     8 | 51.1537 | 34.8723 | 48.8277857142857 | -0.1821 | 33.6204 | 4.64682857142857 |
|     9 | 51.1537 | 34.8723 | 48.495512244898  | -0.1821 | 33.6204 | 5.33667551020408 |
|    10 | 51.1537 | 34.8723 | 48.1632387755102 | -0.1821 | 33.6204 | 6.02652244897959 |
|    11 | 51.1537 | 34.8723 | 47.8309653061225 | -0.1821 | 33.6204 | 6.7163693877551  |
|    12 | 51.1537 | 34.8723 | 47.4986918367347 | -0.1821 | 33.6204 | 7.40621632653061 |
|    13 | 51.1537 | 34.8723 | 47.1664183673469 | -0.1821 | 33.6204 | 8.09606326530612 |
|    14 | 51.1537 | 34.8723 | 46.8341448979592 | -0.1821 | 33.6204 | 8.78591020408163 |
|    15 | 51.1537 | 34.8723 | 46.5018714285714 | -0.1821 | 33.6204 | 9.47575714285714 |
|    16 | 51.1537 | 34.8723 | 46.1695979591837 | -0.1821 | 33.6204 | 10.1656040816327 |
|    17 | 51.1537 | 34.8723 | 45.8373244897959 | -0.1821 | 33.6204 | 10.8554510204082 |
|    18 | 51.1537 | 34.8723 | 45.5050510204082 | -0.1821 | 33.6204 | 11.5452979591837 |
|    19 | 51.1537 | 34.8723 | 45.1727775510204 | -0.1821 | 33.6204 | 12.2351448979592 |
|    20 | 51.1537 | 34.8723 | 44.8405040816327 | -0.1821 | 33.6204 | 12.9249918367347 |
|    21 | 51.1537 | 34.8723 | 44.5082306122449 | -0.1821 | 33.6204 | 13.6148387755102 |
|    22 | 51.1537 | 34.8723 | 44.1759571428571 | -0.1821 | 33.6204 | 14.3046857142857 |
|    23 | 51.1537 | 34.8723 | 43.8436836734694 | -0.1821 | 33.6204 | 14.9945326530612 |
|    24 | 51.1537 | 34.8723 | 43.5114102040816 | -0.1821 | 33.6204 | 15.6843795918367 |
|    25 | 51.1537 | 34.8723 | 43.1791367346939 | -0.1821 | 33.6204 | 16.3742265306122 |
|    26 | 51.1537 | 34.8723 | 42.8468632653061 | -0.1821 | 33.6204 | 17.0640734693878 |
|    27 | 51.1537 | 34.8723 | 42.5145897959184 | -0.1821 | 33.6204 | 17.7539204081633 |
|    28 | 51.1537 | 34.8723 | 42.1823163265306 | -0.1821 | 33.6204 | 18.4437673469388 |
|    29 | 51.1537 | 34.8723 | 41.8500428571429 | -0.1821 | 33.6204 | 19.1336142857143 |
|    30 | 51.1537 | 34.8723 | 41.5177693877551 | -0.1821 | 33.6204 | 19.8234612244898 |
|    31 | 51.1537 | 34.8723 | 41.1854959183674 | -0.1821 | 33.6204 | 20.5133081632653 |
|    32 | 51.1537 | 34.8723 | 40.8532224489796 | -0.1821 | 33.6204 | 21.2031551020408 |
|    33 | 51.1537 | 34.8723 | 40.5209489795918 | -0.1821 | 33.6204 | 21.8930020408163 |
|    34 | 51.1537 | 34.8723 | 40.1886755102041 | -0.1821 | 33.6204 | 22.5828489795918 |
|    35 | 51.1537 | 34.8723 | 39.8564020408163 | -0.1821 | 33.6204 | 23.2726959183673 |
|    36 | 51.1537 | 34.8723 | 39.5241285714286 | -0.1821 | 33.6204 | 23.9625428571429 |
|    37 | 51.1537 | 34.8723 | 39.1918551020408 | -0.1821 | 33.6204 | 24.6523897959184 |
|    38 | 51.1537 | 34.8723 | 38.8595816326531 | -0.1821 | 33.6204 | 25.3422367346939 |
|    39 | 51.1537 | 34.8723 | 38.5273081632653 | -0.1821 | 33.6204 | 26.0320836734694 |
|    40 | 51.1537 | 34.8723 | 38.1950346938776 | -0.1821 | 33.6204 | 26.7219306122449 |
|    41 | 51.1537 | 34.8723 | 37.8627612244898 | -0.1821 | 33.6204 | 27.4117775510204 |
|    42 | 51.1537 | 34.8723 | 37.530487755102  | -0.1821 | 33.6204 | 28.1016244897959 |
|    43 | 51.1537 | 34.8723 | 37.1982142857143 | -0.1821 | 33.6204 | 28.7914714285714 |
|    44 | 51.1537 | 34.8723 | 36.8659408163265 | -0.1821 | 33.6204 | 29.4813183673469 |
|    45 | 51.1537 | 34.8723 | 36.5336673469388 | -0.1821 | 33.6204 | 30.1711653061224 |
|    46 | 51.1537 | 34.8723 | 36.201393877551  | -0.1821 | 33.6204 | 30.861012244898  |
|    47 | 51.1537 | 34.8723 | 35.8691204081633 | -0.1821 | 33.6204 | 31.5508591836735 |
|    48 | 51.1537 | 34.8723 | 35.5368469387755 | -0.1821 | 33.6204 | 32.240706122449  |
|    49 | 51.1537 | 34.8723 | 35.2045734693878 | -0.1821 | 33.6204 | 32.9305530612245 |
|    50 | 51.1537 | 34.8723 | 34.8723          | -0.1821 | 33.6204 | 33.6204          |
+-------+---------+---------+------------------+---------+---------+------------------+

更新：

创建一个接收所需参数的内联表值函数：

CREATE FUNCTION dbo.AtoB (  
@sections int,
@lata float,
@latb float,
@longa float,
@longb float
)
RETURNS TABLE 
AS
RETURN (

SELECT
point,
@lata AS lata,
@latb AS latb,
@lata - ( ( ( @lata - @latb ) / 49 ) * ( point - 1 ) ) AS latitude,
@longa AS longa,
@longb AS longb,
@longa - ( ( ( @longa - @longb ) / 49 ) * ( point - 1 ) ) AS longitude
FROM (
SELECT 
ROW_NUMBER() OVER ( ORDER BY [value] ) AS point 
FROM STRING_SPLIT( REPLICATE( ',', @sections - 1 ), ',' )
) AS n
)
GO

如何调用新的在线TVF:

SELECT * FROM dbo.AtoB ( 50, 51.1537, 34.8723, -0.1821, 33.6204 );

注意：为了更好地反映其目的，我已将@max_results更改为@sections。