Acceptable format for string passed to Stored Proc 1

[GarHeard]

What would be an acceptable format for a string to pass to a parameter @StateF in the following Stored Procedure ?

the last If statement is as follows:

If @StateF Is Not Null
BEGIN
SET @SQL = @SQL + ' AND C.State In ' + @StateF
END

I tried the following string but I wasn't successful.

My application builds a concatenated string named strFallInst and let's say it currently has the following value:

strFallInSt is 'AL','AZ'

I then reformatted the string to pass to the parameter @StateF as followssingle qoutes) Chr$(39)

strStateF = Chr$(39) + "(" + strFallInSt + ")" + Chr$(39)

I also tried surrounding the + "(" + strFallInSt + with Chr$(34) (double quotes) but it didn't work either.

strStateF is passed to the Stored Procedure via the following statement in my application:

.Parameters.Append .CreateParameter("StateF", adVarChar, adParamInput, 200, strStateF)

The stored procedure is as follows ( I stripped out part of the Stored Procedure to show just the part that isn't working, namely the @StateF parameter:

CREATE PROCEDURE dbo.procFAllStates
@RptYear int,
@Prop varchar (5),
@Agg varchar (5),
@IRA varchar (5),
@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]
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.Fall = 1 '

If @StateF Is Not Null
BEGIN
SET @SQL = @SQL + ' AND C.State In ' + @StateF
END

PRINT @SQL
EXEC(@SQL)
GO

 

[JamesLean]

Check out these two FAQs:

Passing a list of values to a Stored Procedure
faq183-3979

Passing a list of values to a Stored Procedure (Part II)
faq183-5207

--James

 

[GarHeard]

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], 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)

 

[GarHeard]

I further modified my SELECT INTO clause to incorporate the SPLIT function.

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
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
INNER JOIN (dbo.Split(@StateF,'') AS SP ON C.State = S.Value
WHERE S.FallCycle = 1 '

and deleted the following If clause

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