Webservice stored procedure error

See more:

SQL-Server-2008R2

Hi,
Anyone familiar with webservice calls inside sql stored procedures. I have this stored procedure (see below) built by someone ages ago. It used to work but suddenly it is giving me error like:
sp_xml_removedocument: The value supplied for parameter number 1 is invalid.

This procedure when called with complete parameters supplied will give me a resultset but not if one is left blank (itemcode) for it to load all the items in the resultset (an optional parameter)

Would you be able to assist me how to fix the code to make the it work (the one with itemcode left blank). Thanks.

USE [BaiadaBusinessInformation]
GO
/****** Object: StoredProcedure [dbo].[CallPriceListWebService] Script Date: 19/08/2014 10:21:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery6.sql|7|0|C:\Users\Losorio\AppData\Local\Temp\~vsC4C1.sql


ALTER Procedure [dbo].[CallPriceListWebService]
@pLocation varchar(50)
, @pdebtorCode varchar(100)
, @pshiptoCode varchar(100)
, @sessionId varchar(100)
, @itemCode varchar(100)
, @response varchar(8000) output
AS
Begin
Begin Try

Declare @obj int
Declare @sURL varchar(200)
Declare @hr int

Declare @startDate varchar(10)
Declare @endDate varchar(10)
DECLARE @DocHandle Int
declare @shipToName varchar(100)

DECLARE @P_XML Xml
declare @LoopCounter int;
declare @priceLsit_XML xml;
Declare @DocHandle1 int

set @startDate = convert(varchar(4), YEAR(getdate())) + '-' + Right('0' + CONVERT(varchar(2), month(GETDATE())), 2) + '-' + right('0' + CONVERT(varchar(2), day(GETDATE())), 2)
set @endDate = convert(varchar(4), YEAR(getdate()+14)) + '-' + Right('0' + CONVERT(varchar(2), month(GETDATE()+14)), 2) + '-' + right('0' + CONVERT(varchar(2), day(GETDATE()+14)), 2)

declare @ResponseInfo table (IID INT IDENTITY(1,1), response text);


--log in to the web service first.
---set @sURL = 'http://b4.baiada.com.au/wsgi/tbm.py?tbmWebService=prclst&resultFormat=xml&sessionId=' + @sessionId + '&location=' + @pLocation + '&startDate=' + @startDate + '&endDate=' + @endDate + '&debtorCode=' + @pdebtorCode + '&shiptoCode=' + @pshiptoCode + '&item=' + @itemCode;
set @sURL = 'http://b4.baiada.com.au/wsgi/tbm.py?tbmWebService=prclst&resultFormat=xml&sessionId=' + @sessionId + '&location=' + @pLocation + '&startDate=' + @startDate + '&endDate=' + @endDate + '&debtorCode=' + LEFT(@pdebtorCode, LEN(@pdebtorCode) - CHARINDEX('~', REVERSE(@pdebtorCode))) + '&shiptoCode=' + LEFT(@pshiptoCode, LEN(@pshiptoCode) - CHARINDEX('~', REVERSE(@pshiptoCode))) + '&item=' + @itemCode;

Exec sp_OACreate 'MSXML2.ServerXMLHttp', @obj out
exec sp_OAMethod @obj, 'Open', null, 'GET', @sUrl, false
exec sp_OAMethod @obj, 'send'

insert into @ResponseInfo
Exec sp_OAGetProperty @obj, 'responseText'; --, @response out

--prepare response for SSRS in tabular format.
SET @P_XML = (select response from @ResponseInfo);

declare @itemInfo table (IID INT IDENTITY(1,1), priceUom varchar(100), desc2 varchar(100), weight float, itemNumber varchar(100), abbrevDesc varchar(100), desc1 varchar(100), overFlow xml)
declare @priceList table (PID INT IDENTITY(1,1), date varchar(10), price float, type varchar(100), priceTable varchar(100), itemCode varchar(100))

EXEC sp_xml_preparedocument @DocHandle OUTPUT, @P_XML


set @shipToName = (select name from OPENXML(@DocHandle, '/root', 2)
with (
name varchar(100)
));

insert into @itemInfo
select * from OPENXML(@DocHandle, '/root/items/item', 2)
with(
priceUom varchar(100),
desc2 varchar(100),
weight float,
itemNumber varchar(100),
abbrevDesc varchar(100),
desc1 varchar(100),
overFlow xml '@mp:xmltext'
)

set @LoopCounter = 1

while (select max(IId) from @itemInfo) >= @LoopCounter
Begin

set @priceLsit_XML = '';
set @priceLsit_XML = (select '<root>' + convert(varchar(8000), overFlow) + '' from @itemInfo where IID = @LoopCounter);

EXEC sp_xml_preparedocument @DocHandle1 OUTPUT, @priceLsit_XML;

insert into @priceList(date, price, type, priceTable)
select * from OPENXML(@DocHandle1, '/root/item/prices/item', 2)
with(
date varchar(10),
price float,
type varchar(100),
priceTable varchar(100)
) ;

update @priceList set itemCode = (select itemNumber from @itemInfo where IID = @LoopCounter) where itemCode is null;
set @LoopCounter = @LoopCounter + 1;
End

select @shipToName as ShipToName, * from @itemInfo A inner join @priceList B on A.itemNumber = B.itemCode order by itemNumber;

Exec sp_OADestroy @obj;
EXEC sp_xml_removedocument @DocHandle;
EXEC sp_xml_removedocument @DocHandle1;

end Try

Begin catch
print 'Issue';
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() AS ErrorMessage;
End Catch
End;