This article describes an alternative way of passing a varying number of input parameters from one T-SQL stored procedure to another by using the built-in OPENXML T-SQL row set statement in order to parse a given XML string. The XML string representation for parameter passings will be in the following format:
<MSG P1="V1" P2="V2" P3="V3" ....... Pn="Vn"/>
where P1,P2...Pn are the parameter names and V1,V2,V3...Vn are their values.
The main advantage of this method is that all input parameters are passed via one XML string. This can create a standard way of passing parameters throughout the entire application, from one procedure to another inside the database or from the client application written in any language (VB, PowerBuilder, C, etc). This is possible because XML strings are a standard method for communicating between different platforms and technologies.
Another advantage of this method is that the parameters can be written and read in any order since the fetching and writing is done by giving the XML tag name. This eliminates the need to worry about parameter order in the calling program. The disadvantage of the method is that it incurs a little overhead in the constructing and parsing of the XML parameter string.
I designed a simple but effective T-SQL stored procedure (sp_get_xml_parameter_value) that gets the XML parameter string value (xmlString) and the name of the tag to be fetched (xmlTag). The result is entered into xmlValue as output. If the tag name does not exist, a NULL value is returned. The procedure creates a temporary table from the OPENXML inner table representation. It then links the ID of the given tag element with its value by doing a self-join on the temporary created OPENXML results, as shown in the procedure.
Here is the procedure code:
Create procedure sp_get_xml_parameter_value (@xmlString varchar(2000), @xmlTag varchar(400), @xmlValue varchar(400) OUTPUT) AS BEGIN declare @Idoc int set nocount on set @xmlValue = NULL EXEC sp_xml_preparedocument @Idoc OUTPUT, @xmlString SELECT [id],parentid,nodetype,localname,[text] into #xmlTable FROM OPENXML (@Idoc, '/MSG',1) select @xmlValue = CONVERT (varchar(400),b.[text]) from #xmlTable a, #xmlTable b where a.localname = @xmlTag and a.nodetype = 2 and a.id = b.parentid and b.nodetype = 3 set nocount off END go
Here is a T-SQL script example for calling the procedure:
declare @xmlString varchar(2000) declare @xmlTag varchar(400) declare @xmlValue varchar(400) set @xmlString = '<MSG ID="ELI LEIBA" COMPANY="IEC" DESCRIPTION="APPLICATION DBA IN COMPANY"/>' set @xmlTag = 'DESCRIPTION' exec sp_get_xml_parameter_value @xmlString ,@xmlTag, @xmlValue OUTPUT print @xmlValue set @xmlTag = 'COMPANY' exec sp_get_xml_parameter_value @xmlString ,@xmlTag, @xmlValue OUTPUT print @xmlValue set @xmlTag = 'ID' exec sp_get_xml_parameter_value @xmlString ,@xmlTag, @xmlValue OUTPUT print @xmlValue
After running the script, we get:
APPLICATION DBA IN COMPANY IEC ELI LEIBA
In conclusion, the procedure I showed here can implement a method of input parameter passing in applications. All input parameters are assembled into a XML string and then sent to the called procedure. It's up to the called procedure to extract whatever parameter it needs by using the sp_get_xml_parameter_value procedure. This procedure can create a standard of parameter passing and also eliminates the need to know the parameters' order.
About the author
Eli Leiba (e-mail: [email protected]) works at the Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the database field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies.