zimmer9
asked on
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)
-------------------------- ----
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
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)
--------------------------
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.