Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Is this SQL Server 2000 or later? If so you could use the FOR XML clause:</p> <pre><code>DECLARE @p1 varchar(100) = 'blah' , @p2 int = 1 , @p3 datetime2(7) = '2011-01-01 13:41' ; SELECT @p1 StringParm , @p2 IntParm , @p3 DateParm FOR XML RAW </code></pre> <p>returns:</p> <pre><code>&lt;row StringParm="blah" IntParm="1" DateParm="2011-01-01T13:41:00"/&gt; </code></pre> <p><strong>Edit</strong></p> <p>Ah, the problem there is that you need to parse out the parameter list as well as the values (which are local) into dynamic SQL (where they'd be out of scope). </p> <p>I suppose you could use INFORMATION_SCHEMA.PARAMETERS to dynamically list the parameters and dbcc_inputbuffers to get the actual values passed. Something like:</p> <pre><code>create procedure junk ( @int INT , @string VARCHAR(20) , @date DATE ) AS BEGIN DECLARE @tmp TABLE ( EventType NVARCHAR(30) , PARMS INT , Info NVARCHAR(2000) ); DECLARE @object NVARCHAR(200); INSERT INTO @tmp EXEC('DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS'); SELECT INFO , 'Call' lType FROM @tmp UNION SELECT STUFF( ( SELECT ', ' + parameter_name FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = OBJECT_NAME(@@procid) ORDER BY ORDINAL_POSITION FOR XML PATH('') ) , 1 , 2 , '' ) , 'Parms'; END </code></pre> <p>That now makes it so that:</p> <pre><code>exec dbo.junk @int = 3, @string = 'hoo', @date = '2/2/2002'; </code></pre> <p>Returns:</p> <pre><code>exec dbo.junk @int = 3, @string = 'hoo', @date = '2/2/2002'; Call @int, @string, @date Parms </code></pre> <p>Which should get you a ways along. The tricky bit is that DBCC_INPUTBUFFERS returns the EXACT call string. So you'd need to write code to parse out the call to match the input line to the parameter list. If you go that route, you'll likely want a stored function that does the parsing. It would likely take the call string and parameter list something like the return values above, match them, and use the FOR XML clause to return the format you want. </p> <p>You could also parse call string in a cursor tied to the parameter list. Then you'd pull the parameters in order and look for the commas and @'s. You could still have trouble with parameter values that included those characters if you didn't take that into account.</p> <p>IMHO, getting that squared away seems like a lot of work compared to a simple select which can almost be copied/pasted from the function header. Of course, if you're talking about a large volume of procedures then it might be worth it. Either way, good luck and thanks for a thought-provoking question.</p>
    singulars
    1. This table or related slice is empty.
    1. POSQL create xml of Parameters dynamically
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJAQFrost
    1. USJAQFrost
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COI'm looking for a script that will do this automatically without knowing the exact number of parameters and their nature. I would like it to be generic, so that I can just add it to the end of a proc and let it do its thing.
      singulars
      1. PO
      1. USBermudaLamb
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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