October 18, 2006 at 5:11 am
Consider i have @strAmt as parameter to a stored procedure.
I need to
Create Usp_TX(@strAmtD varchar(10)
as
declare @doc int,@UpdBal varchar(1500)
set @UpdBal='<ROOT<Upd CrAmt="0" DrAmt="785710.52" LID="1546" CID="BM" FYr="2006-2007"/> <Upd CrAmt="785710.52" DrAmt="0" LID="2241" CID="BM" FYr="2006-2007"/> </ROOT>'
EXEC sp_xml_preparedocument @Doc OUTPUT, @UpdBal
Update Ledger
set AprAmtD= AprAmtD+DrAmt,
AprAmtC =AprAmtC + CrAmt,
CurBalD=CurBalD+DrAmt, CurBalC=CurBalC+CrAmt
from openxml(@Doc,'/ROOT/Upd',3)
with ( CrAmt numeric(18,2),
DrAmt numeric(18,2),
LID varchar(10),
CID varchar(15),
FYr varchar(15) )
where CompanyID=CID and Finyear=FYr and LedgerID=LID
EXEC sp_xml_removedocument @Doc
The above code works fine.
But consider that i get the field name (i.e. AprAmtD dynamically in the variable @strAmtD)
then my code goes like this
declare @strquery varchar(2500)
set @strquery = ' Update Ledger
set ' + @strAmtD + '=' + @strAmtD+ '+DrAmt,' +
@strAmtC+ '=' + @strAmtC+ '+CrAmt,
CurBalD=CurBalD+DrAmt, CurBalC=CurBalC+CrAmt
from openxml('+cast(@Doc as int)+',''/ROOT/Upd'',3)
with ( CrAmt numeric(18,2),
DrAmt numeric(18,2),
LID varchar(10),
CID varchar(15),
FYr varchar(15) )
where CompanyID=CID and Finyear=FYr and LedgerID=LID'
exec (@strquery)
But this does not work.
Any help appreciated.
Thanks
Rohini
October 19, 2006 at 10:15 am
EXEC sp_xml_preparedocument @Doc OUTPUT, @UpdBal creates xml handle for the current session only. Xml handle is session isolated. When SQL executes the dynamic query, it creates a new session and it does not have the xml handle
To solve the issue, you can create a temp table (#t), parsing the xml into the temp table and use the temp table in the dynamic query.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply