You lost me. Are you saying that you do not want a consultant id to display any repromotes if the latest repromote is the current level?

Additionally, what makes this...

Not quite, if the repromote is the currentlevel then don't display. The script works but it is still not suppressing the repromote is at the current level.

--if the consultant has...

You should remove the additional where clause we put into the insert statement and use the code above instead.

This should set you right 😉

To accomplish what you want you will need to filter the query not the insert statement.

your query should look like this:

SELECT * FROM

(SELECT DISTINCT ConB.ConsultantID,

ConB.AchieveLevel,

ConB.AchieveTitle,

ConB.PeriodEndDate,

ConB.RepFlag

FROM #C ConA

INNER JOIN #C...

I get what you are saying now. I thought that you didnt want to see any of the repromotes at the current level.

i had to add the distinct clause to make it work for me.

INSERT INTO @C

SELECT distinct

you may have to do this too.

I edited my post try having the filter in the where clause. I tested this locally with success. Actually I tested both versions successfully.

INSERT INTO #C

SELECT a.ConsultantID

,a.AchieveLevel

,a.AchieveTitle

,a.PeriodEndDate

, a.Repflag

FROM Volume a

INNER JOIN (SELECT x.i.value('.', 'VARCHAR(7)') AS [ConsultantID]

FROM @x.nodes('//i') x(i) ) as b

on a.ConsultantID = b.ConsultantID

WHERE b.ConsultantID IS NOT NULL

AND a.Level <> (SELECT TOP...

Try coping your create table from within the stored procedure and replace the create table #test. Then rename the table to #temp. Maybe something is off with #temp's...

This means that if you run the stored procedure against any one of those number it should return 0 results, which means that the consultant has never been repromoted.

Can you...

If you run this what do you get

SELECT DISTINCT RTRIM(ConsultantID) + '|'

FROM contemp

You can get your results into a string by using the same xml parsers logic.

DECLARE @ConIDs VARCHAR(MAX)

SET @ConIDs =

(SELECT DISTINCT RTRIM(ConsultantID) + '|'

FROM #mytable

FOR XML PATH(''))

select @conids

You...

So if you execute the stored procedure it works correctly but if you try to insert the results into a table it fails?

can you display what your conids look like in string format?

Your insert is completing but the execution of the stored procedure is failing. Do as Jonnie suggested and run the procedre seperately. This way we can isolate the...