Can you post your stored procedure? Or at least the part where you are declaring your output variable and your return statement

Are you trying to do all the Consultants or just a select few? How many consultants are left after you filter your recordset? The XML is only good for...

You can apply your filter here instead of later. Meaning if you implement the filter here then there is no need to filter in the actual query.

INSERT INTO @C

SELECT...

If you ever get into a situation that you need more than 4000 bytes for your string, you can use the nvarchar(max) data type, which holds 2 GB.

Simple, you do XML the reverse way 😉

DECLARE @ConIDs VARCHAR(4000)

SET @ConIDs =...

alorenzini,

I am going to attach the query via a txtfile. I have tested this query successfully. You should be able to see what is happening with yours.

This was my inital thought...

Finally got the post to show up right :hehe:

This process use xml to parse the string to derivce the consultant id that should be included. You can change the delmiter to whatever you like. Keep in mind that...

First you need to get away from the loop, while method works it is not the best solution. I changed my code in light of Marks logic and came...

Alorenzini,

What you can do is execute the stored procedure multiple times and store the results in the temp table each time via a while loop. My preferrend method and what...

I forgot to mention you will need to create the table first. You need to create the table with the correct definitions.

e.g.

CREATE TABLE #temp(

ConsultantID CHAR(7),

Level CHAR(2),

Title CHAR(25),

PeriodEndDate DATETIME

)

GO

INSERT INTO...

You could dump the results of the stored procedure into a temp table and then inner join it to the select.

INSERT INTO #temp

EXEC MyStoredProc 'TestVar'

SELECT a.ConsultantID,

...

No, you would have to make it a function that returns a table. You cannot use a stored procedure in a select statement, but you can; however, use a...

Your best bet would to be to use a non CTE/recursive solution like the one I posted or even better the one Jonnie posted.