Peter,

Sorry for not being clear.

I was refering to Gary's charindex > 0 test.

Why do you want to do a comparison?

DECLARE @b-2 bit

SET @b-2 = CHARINDEX('c', 'abc')

SELECT @b-2

SET @b-2 = CHARINDEX('d', 'abc')

SELECT @b-2

You may need to make sure that parentID <> ChildID in the reference table.

If you have loops in the data I would suggest you get rid of them using iteration.

SELECT

    CAST(LEFT(DataRow, CHARINDEX('A', DataRow) - 1) AS int) AS QuestionNo

    ,CAST(SUBSTRING(DataRow, CHARINDEX('A', DataRow) + 1, 8000) AS bigint) AS AnswerNo

FROM (

        SELECT '02245A555115555155'

    ) AS YourTable (DataRow)

Your basic problem is that you need to carry the current parent value through the recursion.

Also, you would probably find it easier if the Reference table was structured better by containing...

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx

-- Option 1

-- Dynamic SQL

DECLARE @SQLString nvarchar(4000)

    ,@Count int

SET @SQLString = N'SELECT @pCount = COUNT(whatever) FROM ' + @ActiveTable

EXEC sp_executesql @SQLString, N'@pCount int OUTPUT', @Count OUTPUT

SELECT @Count

-- Option 2

-- If the...

or:

UPDATE Membership

SET kMembStatusCode = 2

    ,StatusDate = GETDATE()

WHERE EXISTS (

        SELECT *

        FROM OrgsToAppend A

        WHERE Membership.OrganisationID = A.OrganisationID

            AND A.Member = 1

    )

    AND kOtherClassDescID = @IDTo

    AND MembStatusCode = @Term

Try:

UPDATE B

SET kMembStatusCode = 2

    ,StatusDate = GETDATE()

FROM Membership B

    JOIN OrgsToAppend A

        ON B.OrganisationID = A.OrganisationID

WHERE A.Member = 1

    AND B.kOtherClassDescID = @IDTo

    AND B.MembStatusCode = @Term

Maybe:

SELECT T1.*

FROM YourTable T1

    JOIN (

        SELECT T2.TagName, MAX(T2.[Date]) AS [Date]

        FROM YourTable T2

        GROUP BY T2.TagName

    ) D ON T1.TagName = D.TagName

            AND T1.[Date] = D.[Date]

ORDER BY T1.[Date] -- DESC

>>that way you can put your primary key on 1 column (datatype integer) for better performance when joining tables on that key.

‘That would be an ecumenical matter!’

Personally I would think...

The following may be of help:

http://www.connectionstrings.com/?carrier=textfile

Maybe you want a composite primary key:

ALTER TABLE YourTable

ADD CONSTRAINT PK_YourTable PRIMARY KEY (a, b, c)

This will stop any duplicates.

Look up the CONVERT function. There are a number of different formats. eg:

SELECT CONVERT(char(10), SNDF.DateTimeSampled, 103)

You can use a derived table:

SELECT *

FROM (

        SELECT code1

            ,item

            ,sec_group =

                CASE

                    WHEN acct_sec = 'foo'

                    THEN 'group found'

                    ELSE 'qwerty'

                END

            ,sub_group =

                CASE

                    WHEN group_sec='group found'

                    THEN 'sub group process'

                    ELSE group_sec

                END

        FROM testtable

    ) D