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

Posted on 2005-05-12
Last Modified: 2009-12-16
I am trying to add an array type function named Split into my Access Application as follows:

 @InputText Varchar(104),
 @Delimeter Varchar(10))
   Value Varchar(4000))

 @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

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
     C..State In (Select Value From dbo.Split(@StateF,'')           /*  the second parameter is 2 single quotes */
@RptYear int,
@Prop varchar (3),
@Agg varchar (3),
@IRA varchar (3),
@StateF varchar(200),
@SQL varchar(8000)


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
     AND C..State In (Select Value From dbo.Split(@StateF,'')           /*  the second parameter is 2 single quotes */


Question by:zimmer9
    1 Comment
    LVL 3

    Accepted Solution

    -------------------AND C..State In (Select Value  -------------------------------

    Check After "C" there are two dots


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
    International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now