Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jbkayne
Posting Yak Master
100 Posts |
 Posted - 2002-09-30 : 13:26:07
|
| I would like to create a procedure that takes a procedure name, and a list of parameters in the form of XML elements.For example:exec sp_XMLHandler 'sprocname', '<params> <param1>''foo''</param1> <param2>''bar''</param2></params>'Internal to the procedure, I would then create a string that executes the procedure with the parameters.example:exec sprocname @param1 = 'foo', @param2 = 'bar'I am curious if there is syntax to OPENXML that would allow me to do this without knowing the parameter names. Currently I have to rely on ordinal position. (i.e. exec sprocname 'foo', 'bar')I noticed that by changing the xml into the following I can accomplish this, but would really like to get it working with the above XML scheme.exec sp_XMLHandler 'sprocname', '<params> <param name="param1" value="''foo''" /> <param name="param2" value="''bar''" /></params>' |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-09-30 : 14:45:54
|
| If the parameters are all uniquely named, then I don't think it can be done. However, if each value is just a member of a <parameters> collection, then you can use OPENXML to parse them into a table (or as a derived table/subquery) for use in your procedure.Can you post the rest of the procedure and what you're using the parameters for? |
 |
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-30 : 15:03:12
|
What are you actually trying to do ? Why the xml parameter list ?Anyway, just for fun DECLARE @sproc varchar(50) DECLARE @idoc intDECLARE @doc varchar(500)DECLARE @params varchar(500)DECLARE @sql nvarchar(1000)SET @doc ='<params> <param1>''foo''</param1><param2>''bar''</param2></params>'SET @sproc = 'foobar'EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT @params = COALESCE(@params,'')+CAST([text] as varchar(50))+',' FROM OPENXML (@idoc, '//params/*',2)WHERE nodetype=3EXEC sp_xml_removedocument @idoc SELECT @sql = 'exec ' + @sproc + ' ' + LEFT(@params,LEN(@params)-1)SELECT @sql--exec master.dbo.sp_executesql @sql HTHJasper Smith |
|
|
|
jbkayne
Posting Yak Master
100 Posts |
Posted - 2002-09-30 : 15:30:55
|
| Jasper,I am working on a project that was based on a proprietary XML caching engine. (Prior .NET) It just so happens, that the data retrieval request was sent to the database in a similar format. Until I get a chance to rewrite the framework in C# using XMLSQL3.0 managed classes, I wanted a way to at least call the internal procedures by @param = value verus. ordinal position. Rob, What Jasper posted is essentially what I currently have. I am just still looking for a way to convert the resulting statement:exec foobar 'foo','bar'intoexec foobar @param1 = 'foo', @param2 = 'bar'Hope that helps clarify, and thanks to both of you! |
|
|
|
jasper_smith
SQL Server MVP & SQLTeam MVY
846 Posts |
Posted - 2002-09-30 : 15:55:42
|
Sorry, didn't read the question properly  DECLARE @sproc varchar(50) DECLARE @idoc intDECLARE @doc varchar(500)DECLARE @params varchar(500)DECLARE @sql nvarchar(1000)SET @doc ='<params> <param1>''foo''</param1><param2>''bar''</param2></params>'SET @sproc = 'foobar'EXEC sp_xml_preparedocument @idoc OUTPUT, @docSELECT @params = COALESCE(@params,'')+' @'+ CAST(x1.localname as varchar(50))+'='+ CAST(x2.[text] as varchar(50))+','FROM (SELECT * FROM OPENXML (@idoc, '//params/*',2)) x1JOIN (SELECT * FROM OPENXML (@idoc, '//params/*',2)) x2ON x2.parentid=x1.[id] AND x1.nodetype=1 AND x2.nodetype=3EXEC sp_xml_removedocument @idoc SELECT @sql = 'exec ' + @sproc + ' ' + LEFT(@params,LEN(@params)-1)SELECT @sql Result : exec foobar @param1='foo', @param2='bar'HTHJasper Smith |
|
|
|
|
|
|
|
|
OPENXML Syntax Challenge