Syntax error adding a user defined function to a stored procedure. The function builds an array of state abbreviateions into a table format.

I am trying to add an array type function named Split into my Access Application as follows:

CREATE FUNCTION SPLIT (
 @InputText Varchar(104),
 @Delimeter Varchar(10))
 
RETURNS @Array TABLE (  
   Value Varchar(4000))
As
BEGIN
 
  DECLARE

 @Pos Int,
 @End Int,
 @TextLength Int,
 @DelimLength Int

Set @TextLength = DataLength(@InputText)


If @TextLength = 0 RETURN

Set @Pos = 1
Set @DelimLength = DataLength(@Delimeter)

IF @DelimLength = 0 BEGIN
  WHILE @Pos <= @TextLength BEGIN
            INSERT @Array (Value)  Values (SubString(@InputText,@Pos,2))
            SET @Pos = @POS + 2
  END        
END

RETURN
END
---------------------------------------------------------------------------------------
The intent is to call the Split function and pass it a string of 2 character state abbreviations into an array and see if each (customer record) C.State value is in this array.
The test is performed in the following If test in my stored procedure:

Do you know why the following statement in my stored procedure is giving me a syntax error as follows:
Is it possible to peform this type of operation using a function ?

Microsoft QLFDMO (ODBC SQLState:42000)
Incorrect syntax near 'C'.
Incorrect syntax near the keywork 'END'

If @StateF Is Not Null
   BEGIN
     C..State In (Select Value From dbo.Split(@StateF,'')           /*  the second parameter is 2 single quotes */
   END
------------------------------------------------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200),
@SQL varchar(8000)

AS

If exists(select * from dbo.sysobjects where name = 'tblFAllStates' and type = 'U')
Drop table tblFAllStates

Set @SQL =
      'SELECT Null As Title, C.FirstName AS [First Name], C.MiddleInitial AS MI, C.LastName AS [Last Name], C.SecondNameFirst AS [Second Name First], C.SecondNameMid AS [Second Name Mid],
       C.SecondNameLast AS [Second Name Last], Null AS Relation, C.Address1 AS [Address 1], C.Address2 AS [Address 2], C.Address3 AS [Address 3], C.Address4 AS [Address 4], C.City, C.State,
       C.Zip, C.SSN, Null AS [DDA Number], Null As [Check/Cert Number], C.DateLost AS [Date Lost], Null As [Date Opened], Right(C.OfficeNumber,3) + '' '' + C.CustomerNumber AS [Account Number],
       C.DateOfBirth, P.PropertyType AS [Property Type], Null As [Property Status], P.CUSIP, P.SecurityName AS [Security Name], Null AS [Sub-Issue], P.MarketValue AS [Market Value],
       P.ClosePrice AS [Market Price], P.Quantity AS [Shares], P.CashBalance AS [Dollar Amount], C.DateOfBirth AS [Date Of Birth], P.IraCode, P.PlanNumber
       INTO tblFAllStates
       FROM tblStatesAll
       AS S INNER JOIN (tblCustomers AS C INNER JOIN tblProducts AS P ON C.CustomerNumber = P.CustomerNumber)  ON S.StateFS = C.State WHERE S.FallCycle = 1 '
...

If @StateF Is Not Null
   BEGIN
     AND C..State In (Select Value From dbo.Split(@StateF,'')           /*  the second parameter is 2 single quotes */
   END

PRINT @SQL
EXEC(@SQL)
------------------------------

zimmer9Asked:
Who is Participating?
 
r_a_j_e_s_hCommented:
-------------------AND C..State In (Select Value  -------------------------------

Check After "C" there are two dots

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.