title: SQL Function call in Select Statement to populate Reporting Services Report
body: <p>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.</p>
  
  <p>Here is the function
      USE [CUDatabase]
      GO</p>
  
  <pre><code>/****** 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&lt;=@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
  </code></pre>
  
  <p>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</p>
  
  <p>Here is the dataset, I need to loop through each unique_nbr and return the check digit.</p>
  
  <pre><code>`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 &gt;= '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`
  </code></pre>
  
  <p>Thanks for any help</p>