No rows returned by Stored procedure using variable string parameter 1

[GarHeard]

I execute the following statement to test my stored procedure dbo.FAllStates and function dbo.SPLIT in query analyzer as follows:

EXEC dbo.procFAllStates 2005,'A',NULL,NULL,'''''AL'''',''''AR'''',''''AZ''''',NULL

the result of the generated sql code is as follows: (0 row(s) affected)

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
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('''AL'',''AZ'',''CA''',',') AS SP ON C.State = SP.Value
WHERE S.FallCycle = 1

(0 row(s) affected)
--------------------
If I remove the line of code: INNER JOIN dbo.SPLIT('''AL'',''AZ'',''CA''',',') AS SP ON C.State = SP.Value

then I get back:
(4280 row(s) affected)
The Into Table created named dbo.tblFAllStates includes records for the States of AZ AND CA
-----------------------------------------------------------

The function the stored procedure uses called dbo.Split is 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
------------------------------------------------
C.State is defined as nvarchar 2 on the tblCustomers table.

 

[vongrunt]

SPLIT() function obviously doesn't work. @Pos is never set to position of found token (CHARINDEX()?), plus @InputText is declared as varchar while your code assumes it is nvarchar (used DATALENGTH(), @Pos gets incremented by 2 etc).

Also: remove quotes from list of states (should be 'AL,AZ,CA').


------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)