SQL Server Performance

Need help with a sproc using a xml as input parm

Discussion in 'Non-Transact SQL Developer Performance Tuning' started by Pedro, Mar 4, 2005.

  1. Pedro New Member

    I#%92m new to Vb.net 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 @nError=@@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=':(' />]
  2. satya Moderator

    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.

Share This Page