Databases
StackOverflow2013
Postsdbo
POSQL create xml of Parameters dynamically
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL create xml of Parameters dynamically
    text
    copied!<p>Is there a way to query the parameters passed into a stored procedure and return them as XML without creating a string of the parameters and then casting that as xml? I'm looking for something generic, that will work for most SPs without having to physicially code it each time?</p> <p>I have a bunch of stored procedures that access and modify verify specific information. At the end of the SPs I want to insert into a logging table the name of the SP, and the parameters (in xml) that were used to invoke the SP. I know how to get the name of the SP, and I know how to get a list of the parameters for the SP. What I want is a way to mash it all into XML along the actual values of the parameters that were passed.</p> <p>I'm looking for something that does this, without the manual coding of each parameter:</p> <pre><code>DECLARE @L_Data varchar(1500) SET @L_Data = '&lt;parms&gt;' + CASE WHEN @ParamRegStationID IS NULL THEN '' ELSE ',@ParamRegStationID=''' + Convert(varchar, @ParamRegStationID) + '''' END + CASE WHEN @ParamScheduleID IS NULL THEN '' ELSE ',@ParamScheduleID=''' + Convert(varchar, @ParamScheduleID) + '''' END + CASE WHEN @ParamPatientID IS NULL THEN '' ELSE ',@ParamPatientID=''' + Convert(varchar, @ParamPatientID) + '''' END + CASE WHEN @ParamHISPatientID IS NULL THEN '' ELSE ',@ParamHISPatientID=''' + @ParamHISPatientID + '''' END + CASE WHEN @ParamEvent IS NULL THEN '' ELSE ',@ParamEvent=''' + @ParamEvent + '''' END + '&lt;/parms&gt;' </code></pre> <p>This doesn't work, and it isn't as elegant as what I'm hoping for. However, here is an example illustrating what I'm trying to ultimately get to. It creates the temp table, but doesn't add the parameters to it as columns, so I can later extract it as XML.</p> <pre><code> ALTER PROC uspTest @ParamID as bigint=null, @ParamXYZ as varchar(255)=null as -- PROC Does whatever it is going to do .... DECLARE @ProcName varchar(128), @ParmName varchar(128), @ParmType varchar(128), @ParmLen int, @ParmSQL varchar(1000) select @ProcName=OBJECT_NAME(@@PROCID) --select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME=@ProcName DECLARE csrParms CURSOR FOR select PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME=@ProcName and PARAMETER_MODE='IN' ORDER BY ORDINAL_POSITION FOR READ ONLY OPEN csrParms FETCH NEXT FROM csrParms INTO @ParmName, @ParmType, @ParmLen CREATE TABLE #Parms(ID int identity(1,1), Created DateTime) INSERT INTO #Parms select GETDATE() WHILE @@FETCH_STATUS = 0 BEGIN -- GET Parm value and format as xml attribute to save parm SET @ParmSQL = 'ALTER TABLE #Parms add ' + @ParmName + ' varchar(' + CAST(ISNULL(@ParmLen, 128) as varchar(128)) + ') NULL ' print @ParmSQL EXEC (@ParmSQL) SET @ParmSQL = 'UPDATE #Parms SET ' + @ParmName + ' = ''????''' print @ParmSQL --EXEC (@ParmSQL) FETCH NEXT FROM csrParms INTO @ParmName, @ParmType, @ParmLen END SET @ParmSQL = CAST((select * from #Parms FOR XML RAW) as varchar(1000)) select @ParmSQL CLOSE csrParms DEALLOCATE csrParms </code></pre> <p>This is close to what I'm looking for, I need to know how to replace the ??? with the current value of the parameter dynamically though.</p> <pre><code> ALTER PROC uspTest @ParamID as bigint=null, @ParamXYZ as varchar(255)=null as -- PROC Does whatever it is going to do .... DECLARE @ProcName varchar(128), @ParmName varchar(128), @ParmType varchar(128), @ParmLen int, @ParmSQL varchar(1000) select @ProcName=OBJECT_NAME(@@PROCID) --select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE' and ROUTINE_NAME=@ProcName set @ParmSQL = ' CREATE TABLE #Parms(ID int identity(1,1), Created DateTime, ' + STUFF((select (', ' + REPLACE(PARAMETER_NAME,'@','') + ' varchar(' + CAST(ISNULL(CHARACTER_MAXIMUM_LENGTH, 128) as varchar(128)) + ') NULL ') from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='uspTest' and PARAMETER_MODE='IN' order by ORDINAL_POSITION for XML path(''), type).value('.', 'varchar(max)'), 1, 2, '') + '); ' + 'INSERT INTO #Parms (Created) select GETDATE(); ' + STUFF((select ('; UPDATE #Parms SET ' + REPLACE(PARAMETER_NAME,'@','') + ' = ''???''') from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='uspTest' and PARAMETER_MODE='IN' order by ORDINAL_POSITION for XML path(''), type).value('.', 'varchar(max)'), 1, 2, '') + '; select CAST((select * from #Parms FOR XML RAW) as varchar(1000));' print @ParmSQL EXEC (@ParmSQL) </code></pre> <p>When I execute the proc as:</p> <pre><code>EXEC uspTest 1, 'test' </code></pre> <p>Returns:</p> <blockquote> <p>&lt;row ID="1" Created="2012-04-20T09:44:43.700" ParamID="???" ParamXYZ="???"/&gt;</p> </blockquote> <p>Prints out:</p> <pre><code>CREATE TABLE #Parms(ID int identity(1,1), Created DateTime, ParamID varchar(128) NULL , ParamXYZ varchar(255) NULL ); INSERT INTO #Parms (Created) select GETDATE(); UPDATE #Parms SET ParamID = '???'; UPDATE #Parms SET ParamXYZ = '???'; select CAST((select * from #Parms FOR XML RAW) as varchar(1000)); </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload