As far as your XML code goes, I don't see anything wrong with what you're doing.

I'd really like to see the XML string that's being passed to the report.

How about trying this code out? (I consolidated your code into one select statement, and formatted it some to make it easier for me to read).

When you call this from your report, you should get the XML string that you're passing to the procedure. Can you copy it and put it up here?

[Code]

ALTER proc [dbo].[spCUSTOMER_PROMO] @C XML

--, @INPUT_DATE DATETIME

AS

declare @c1 varchar(max)

set @c1 = convert(varchar(max), @C)

RaisError(@c1, 16, 1)

declare @temp varchar(25) -- need something since two fields are never inserted into, but needed for output

select

CS.CUSTOMER_NAME CUSTOMER,

TA.CUSTOMER_NUMBER,

CASE WHEN TP.TP_PROGRAM_ID = 'PREMIUM' THEN TD.TP_DISCOUNT_AMOUNT ELSE '' END PREM,

CONVERT(VARCHAR,TP.TP_EFFECTIVE_START_DATE,101) + ' - ' + CONVERT(VARCHAR,TP.TP_EFFECTIVE_END_DATE,101) PREM_VALID,

TA.TP_PROMOTION_CODE,

CASE WHEN TP.TP_PROGRAM_ID = 'FREIGHT' THEN TD.TP_DISCOUNT_AMOUNT ELSE '' END FREIGHT,

@temp FREIGHT_VALID,

@temp FREIGHT_CODE

FROM TP_CUSTOMER_ASSOCIATIONS AS TA

INNER JOIN TP_PROMOTIONS AS TP ON TP.TP_PROMOTION_CODE = TA.TP_PROMOTION_CODE

INNER JOIN CUSTOMERS AS CS ON TA.CUSTOMER_NUMBER = CS.CUSTOMER_NUMBER

INNER JOIN TP_DISCOUNT_DETAILS AS TD ON TD.TP_PROMOTION_CODE = TP.TP_PROMOTION_CODE

INNER JOIN @C.nodes('/C/CUSTOMER') as m(item) on cast(CS.CUSTOMER_NUMBER as int) = m.item.value('CUSTOMER_NUMBER[1]','integer')

WHERE '03/10/2008' BETWEEN TP.TP_EFFECTIVE_START_DATE AND TP.TP_EFFECTIVE_END_DATE

AND TP.TP_PROGRAM_ID IN('PREMIUM', 'FREIGHT')

ORDER BY CS.CUSTOMER_NAME

-- =Code.ReturnXML(Parameters!C.Value,"C","CUSTOMER","CUSTOMER_NUMBER")

[/Code]