Select 语句中的 SQL 函数调用以填充 Reporting Services 报告
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13369198/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Function call in Select Statement to populate Reporting Services Report
提问by Rayna Oesterreicher
I'm trying to return a specific digit for each number in a dataset, I've written an SQL function and now I need to be able to do the calculation in the function for each number in the dataset. Can you please point me in the correct direction? I don't know if I should create a temp table then join that, if I should just write a vb function within Reporting Services and do that or if I just need to start over.
我试图为数据集中的每个数字返回一个特定的数字，我已经编写了一个 SQL 函数，现在我需要能够在函数中为数据集中的每个数字进行计算。你能指出我正确的方向吗？我不知道我是否应该创建一个临时表然后加入它，如果我应该在 Reporting Services 中编写一个 vb 函数并这样做，或者我是否只需要重新开始。
Here is the function USE [CUDatabase] GO
这是函数 USE [CUDatabase] GO
/****** Object: UserDefinedFunction [dbo].[fn_Check_Digit] Script Date: 11/13/2012 14:40:59 ******/ SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fn_Check_Digit]
(
@unique_NBR VARCHAR(MAX)
)
RETURNS @Values TABLE
(
check_digit int,
unique_nbr int
)
AS
BEGIN
-- set up working variables
DECLARE @LEN AS INT
DECLARE @INDEX AS INT
DECLARE @CHAR AS VARCHAR(1)
DECLARE @POSITION AS INT
DECLARE @VALUE AS INT
DECLARE @SUBTOTAL AS INT
DECLARE @BASE AS INT
DECLARE @CHECK_DIG AS INT
SET @LEN = LEN(@MEMBER_NBR)
SET @INDEX = 1
SET @POSITION = 0
SET @VALUE = 0
SET @SUBTOTAL = 0
SET @BASE =0
SET @CHECK_DIG = 0
-- iterate until we have no more characters to work with
WHILE @index<=@len
BEGIN
SET @char = SUBSTRING(@unique_NBR,(@len-@POSITION),1)
select @value = (SELECT scd.dig_mul_value
FROM CUDatabase.DBO.sdcCheckDigit SCD
WHERE SCD.dig_place = @index)
set @value = @value * @char
SET @index = @index + 1
SET @POSITION = @POSITION + 1
SET @SUBTOTAL = @VALUE + @SUBTOTAL
END
SET @BASE = ((@SUBTOTAL/10)+1)*10
IF @BASE -@SUBTOTAL = 10
SET @CHECK_DIG = 0
ELSE
SET @CHECK_DIG = @BASE-@SUBTOTAL
INSERT INTO @Values (check_digit, unique_nbr) VALUES (CAST(@CHECK_DIG AS int),@unique_NBR)
RETURN
END
GO
The table that is in the select statement of that function has the following values in it: dig_place dig_mul_value 1 7 2 3 3 1 4 7 5 3 6 1 7 7 8 3 9 1
该函数的 select 语句中的表具有以下值： dig_place dig_mul_value 1 7 2 3 3 1 4 7 5 3 6 1 7 7 8 3 9 1
Here is the dataset, I need to loop through each unique_nbr and return the check digit.
这是数据集，我需要遍历每个 unique_nbr 并返回校验位。
`SELECT I.D1NAME,
IA.ADDRESS_ID,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.CITY,
A.STATE,
A.ZIP_STR,
TL.COMPANY_NAME,
TL.COMPANY_DESCRIPTION,
TL.EFFECTIVE_ENTRY_DATE,
TL.AMOUNT,
TL.ACCOUNT_NBR,
TL.ACCT_DBRN
FROM MEMBERSHIPPARTICIPANT MP
JOIN INDIVIDUAL I ON
I.INDIVIDUAL_ID = MP.INDIVIDUAL_ID
AND I.DL_LOAD_DATE = MP.DL_LOAD_DATE
JOIN INDIVIDUALADDRESS IA ON
IA.INDIVIDUAL_ID = I.INDIVIDUAL_ID
AND IA.IS_PRIMARY = 1
AND IA.DL_LOAD_DATE = I.DL_LOAD_DATE
JOIN ADDRESS A ON
A.ADDRESS_ID = IA.ADDRESS_ID
AND A.DL_LOAD_DATE = IA.DL_LOAD_DATE
JOIN (SELECT EFT.unique_NBR,
EFT.ACCOUNT_NBR,
EFT.ACH_SDC_NBR,
EFT.COMPANY_NAME,
EFT.COMPANY_DESCRIPTION,
EFT.INDIVIDUAL_ID_NBR,
EFT.INDIVIDUAL_NAME,
EFT.XPTIMESTAMP,
EFT.STANDARD_ENTRY_CLASS,
EFT.ROUTING_NUMBER,
EFT.ACCT_DBRN,
EFT.AMOUNT,
EFT.EFFECTIVE_ENTRY_DATE
FROM EFTTRANSACTION EFT
WHERE EFT.ROUTING_NUMBER = 999999999
AND EFT.STANDARD_ENTRY_CLASS IN ('WEB','TEL')
AND EFT.EFFECTIVE_ENTRY_DATE >= '11/01/2012') TL
ON T L.unique_NBR = MP.unique_NBR
WHERE MP.DL_LOAD_DATE = (SELECT MAX(DL_LOAD_DATE) FROM MEMBERSHIPPARTICIPANT)
AND MP.PARTICIPATION_TYPE = 101
--AND MP.unique_NBR = 9835
ORDER BY MP.unique_NBR`
Thanks for any help
谢谢你的帮助
采纳答案by RichardTheKiwi
All you have to do is call the SQL function you have already created, i.e.
你所要做的就是调用你已经创建的SQL函数，即
SELECT I.D1NAME,
IA.ADDRESS_ID,
A.ADDRESS1,
A.ADDRESS2,
A.ADDRESS3,
A.CITY,
A.STATE,
A.ZIP_STR,
TL.COMPANY_NAME,
TL.COMPANY_DESCRIPTION,
TL.EFFECTIVE_ENTRY_DATE,
TL.AMOUNT,
TL.ACCOUNT_NBR,
TL.ACCT_DBRN,
dbo.fn_Check_Digit(L.unique_NBR) CheckDigit
FROM .....
