How can I load the consid into the Repromote Proc?

I haven't change anything in the Repromote proc, this is code as it now:

ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions2]

@CONSULTANTID VARCHAR(MAX)

AS

SET NOCOUNT ON

DECLARE @x XML

SET @x = ' '

CREATE TABLE #C

(ConsultantID CHAR(20)

,AchieveLevel CHAR(2)

,AchieveTitle CHAR(50)

,PeriodEndDate...

I change the NULLS and when I did this in the main proc:

CREATE TABLE #TEMP (

ConsultantID CHAR(20),

AchieveLevel CHAR(2),

AchieveTitle CHAR(50),

PeriodEndDate DATETIME,

RepFlag Varchar(2)

)

INSERT INTO #Temp

EXEC uspS_DownlineRepromotions2 @ConIDs

Select * from #Temp

It errors...

It's still doing it. This is the what the #Temp looks like now in the main proc:

CREATE TABLE #TEMP (

ConsultantID CHAR(20),

AchieveLevel CHAR(2),

AchieveTitle CHAR(50),

PeriodEndDate DATETIME,

RepFlag Varchar(2)

)

And this is what the Repromote...

This is the temp table from the Repromotion2 proc:

CREATE TABLE #C

(ConsultantID CHAR(7)

,AchieveLevel CHAR(2)

,AchieveTitle CHAR(25)

,PeriodEndDate DATETIME

,RepFlag VarChar(2))

THis is the temp table from the main proc:

CREATE TABLE #TEMP (Row_ID INT IDENTITY(1,1),

ConsultantID CHAR(20),

AchieveLevel...

I went to ConsultantID that had fewer consultants on their downline (28) and it gave me this new error:

Warning: Null value is eliminated by an aggregate or other SET operation.

(28...

I am thinking the XML string is not going to work now that I am trying to put together like 983 Consultant IDs:

Msg 9400, Level 16, State 1, Procedure uspS_DownlineRepromotions2,...

You bet I will and thanks for everybody helps. It has been quite the learning experience. I am sure I will need more help as I go foward from here.:)

I missed it. It is working now. It flys with 2 IDs (<1 sec)so the real test will be to get a string of 50 or more IDs and...

I also changed to use a temp table instead but still have the previous issue of no data.

I took two IDs 3 minutes and 30 seconds to run. I need the hook to the volume table because that is where the initial Consultant data is (AChieveLevel, AchieveTitle...

It looks like its returning what I want but it is way to slow. It took 50 second for one consultantID and 3.45 sec for two ConsultantIDs I can't even...

This is the code as it sits now:

ALTER PROCEDURE [consultantreports].[uspS_DownlineRepromotions2]

@CONSULTANTID VARCHAR(8000)

AS

SET NOCOUNT ON

DECLARE @x XML

SET @x = ' '

DECLARE @C TABLE(

ConsultantID CHAR(7),

Level CHAR(2),

Title CHAR(25),

PeriodEndDate DATETIME

)

INSERT INTO @C

SELECT a.ConsultantID,a.AchieveLevel,a.AchieveTitle,a.PeriodEndDate

FROM volume a

LEFT...

I tried to add the new proc into my main proc and recieved the following error:

Msg 9400, Level 16, State 1, Procedure uspS_DownlineRepromotions2, Line 7

XML parsing: line 1, character 4000,...

In the latest repromotion proc something is not quite right. THis part:

INSERT INTO @C

SELECTa.ConsultantID

,a.AchieveLevel

,a.AchieveTitle

,a.PeriodEndDate

FROM volume a

LEFT OUTER JOIN Volume d ON a.ConsultantID = d.ConsultantID

WHERE EXISTS

(SELECT * FROM volume b...