October 18, 2006 at 5:12 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 18, 2006 at 7:37 am
Hi,
you write:
"But consider that i get the field name (i.e. AprAmtD dynamically in the variable @strAmtD)"
Does that mean @strAmtD contains BOTH the column name and the value?
If so, then you need to parse it first into two parts. What you are doing now in the dynamic SQL is using the same string as a column name and a value for it. That can't be correct. You need something like
set @strquery = ' Update Ledger
set ' + SUBSTRING( @strAmtD, x, y) + '=' + SUBSTRING( @strAmtD, z, q)+ '+DrAmt,' .....
Not knowing the structure of @strAmtD, I have no idea how to parse it best. This is just a hint in what direction to explore. To be honest, I don't think column name and value should be passed together in one parameter - but maybe it is something independent from you, one of these facts you can't change. If you can pass it as two parameters, do so and you'll be able to avoid this decomposition of a string.
October 18, 2006 at 7:50 am
I modified your query a lil since I don t have the target table for update
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
declare @strAmtD varchar(250)
SET @strAmtD='LID'
declare @strAmtC varchar(250)
SET @strAmtC='CID'
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition = N'@docI int'
declare @strquery nvarchar(2500)
set @strquery = ' SELECT ' + @strAmtD+ ',' + @strAmtC+ ', DrAmt
from openxml(@docI,''/ROOT/Upd'',3)
with ( CrAmt numeric(18,2),
DrAmt numeric(18,2),
LID varchar(10),
CID varchar(15),
FYr varchar(15) )'
PRINT @strquery
EXECUTE sp_executesql @strquery, @ParmDefinition,@docI = @doc
Vasc
October 18, 2006 at 10:00 pm
Thank a lot Vasc. It works . Great. !!
Rohini
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply