Need help with a sproc using a xml as input parm | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Need help with a sproc using a xml as input parm

I#%92m new to or VB in general and need help executing a sproc with a well-formed XML input<br /><br />Here is the short version of it<br /><br /><br />Alter procedure dob.RTC_Customer_Update<br />( @XMLData ntext = null,<br /> @vUser varchar(10))<br /><br /><br />as<br />declare @nError int<br />declare @sMode varchar(10)<br />declare @vnName varchar(100)<br />declare @sErrorDesc varchar(100)<br />declare @dTodate datetime,<br /> @XMLHnd int<br /><br />declare @TMp table (NCUSTKEY numeric ( 6 ) ,<br /> VCNTRYCODE varchar ( 2 ) ,<br /> VIDTYPE varchar ( 1 ) ,<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />Mode int)<br /><br />begin transaction<br />set nocount on <br /> if @XMLData is null <br /> begin <br /> select @nError=5<br />end<br /><br />select @dToday = getdate()<br />EXEC sp_xml_preparedocument @XMLHnd OUTPUT, @XMLData<br />Inset into @Tmp (NCUSTKEY ,<br /> VCNTRYCODE , <br /> VIDTYPE , <br /> .<br /> .<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br /> Mode)<br />Select NCUSTKEY ,<br /> VCNTRYCODE , <br /> VIDTYPE , <br /> .<br /> .<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br /> Mode<br />From OPENXML (@XMLHnd, ‘/ROOT#%92,1)<br /> WITH(NCUSTKEY numeric ( 6 ) ,<br /> VCNTRYCODE varchar ( 2 ) , <br /> VIDTYPE varchar ( 1 ) , <br /> .<br /> .<br /> .<br /> .<br /> .<br /> .<br /> .<br /> .<br /> .<br /> .<br /> Mode int)<br />Select @[email protected]@Error<br />If @nError != 0 goto exitError<br />EXE sp_xml_removedocument @XMLHnd<br /><br />————————————————————————————-<br /><br />select @sMode =#%92 update ‘<br /><br />update Customer NCUSTKEY =T. NCUSTKEY ,<br /> VCNTRYCODE=T.VCNTRYCODE , <br /> VIDTYPE =T. VIDTYPE , <br /> .<br /> .<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br />.<br /> VCITY=T.VCITY<br />From Customer<br /> Join @Tmp T<br /> On Customere.nCustKey=T.CustKey <br />Were T.MODE=2<br /> <br /><br />[<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />][<img src=’/community/emoticons/emotion-6.gif’ alt=’:(‘ />]
Not sure whether this tip helps from one of the technet chat:#
If you parameterize your queries it should not be a problem. For example, (I am assuming you are using ADO) instead of running your query with Connection.Execute, run the query via Command.Execute and pass in your own data using the Parameters collection. This will have the added benefit of reducing compile time for repeated executions of the same query. If you must include string literals in-line in a query instead of as an explicit parameter, you might consider using the REPLACE function to replace all quote characters ("’") with two repeated quote characters ("”"). Versions of this function exist in both T-SQL and VB. Satya SKJ
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.