How to Find Parameters Used in Executed Stored Procedure or Command

I suggest a different tack altogether. Instead of naming 18,000 parameters why not make use of table-valued parameters? I'm making some leaps here about what exactly you're using all these parameters for (since you so handily anonymized them for us :-)), but if you create these types:

CREATE TYPE dbo.VarcharParameters AS TABLE
(
  ParamName  SYSNAME,
  ParamValue VARCHAR(100)
);

CREATE TYPE dbo.BitParameters AS TABLE
(
  ParamName  SYSNAME,
  ParamValue BIT
);

Then change the procedure as follows (please note the comments inline about how to deal with stuff in the TVPs):

CREATE PROCEDURE dbo.ObviouslyAnonymizedProcedure2
  @SchemaID      INT                   = NULL,
  @TypeDesc      NVARCHAR(60)          = NULL,
  @VCParams      dbo.VarcharParameters READONLY,
  @BitParams     dbo.BitParameters     READONLY,
  @paramStartRow INT,
  @paramMaxRows  INT
AS
BEGIN
  SET NOCOUNT ON;
  SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

  DECLARE 
    @sql      NVARCHAR(MAX) = N'',
    @From     NVARCHAR(MAX) = N'',
    @Where    NVARCHAR(MAX) = N'',
    @LF       CHAR(2)       = CHAR(13) + CHAR(10),
    @Tab      CHAR(1)       = CHAR(9),
    @FLOuter  NVARCHAR(MAX),
    @FLInner  NVARCHAR(MAX);

  DECLARE @LFTab CHAR(3) = @LF + @Tab;

  SET @FLOuter = @LFTab + '  t1.name' 
        + @LFTab + ', t1.object_id'
        + @LFTab + ', SCHEMA_NAME(t1.schema_id) AS schema_name'
        + @LFTab + ', t1.type_desc' 
        + @LF; 

  SET @FLInner = @LFTab + '  t0.name'
        + @LFTab + ', t0.object_id'
        + @LFTab + ', t0.schema_id'
        + @LFTab + ', t0.type_desc'
        + @LF; 

  SET @From = N' From sys.objects as t0 with(nolock) ' + @LF;

  IF @SchemaId IS NOT NULL
  BEGIN
    SET @Where = @Where + ' AND t0.schema_id = @SchemaId';
  END 

  IF @TypeDesc IS NOT NULL
  BEGIN
    SET @Where = @Where + ' AND t0.type_desc = @TypeDesc'
  END

  -- obviously you need a bunch more of these, and I'm making
  -- a half-educated guess about how the bit params are used:
  IF EXISTS (SELECT 1 FROM @BitParams WHERE ParamName = 'paramIsView' AND ParamValue = 1)
  BEGIN
    SET @Where += ' AND t0.type_desc = ''VIEW'''
  END

  -- and I'm not clear exactly what you're doing with the varchar params,
  -- but if you give some more clues I'm sure we can work that out too.
  -- It may be very simple to build a string from those, without having to
  -- reference every single one of them by name, depending on what they do.

  SET @sql = 'SELECT ' + @FLOuter + ' FROM ( SELECT ROW_NUMBER() OVER 
      (ORDER BY t0.[object_id]) AS rn, ' + @FLInner +
    @From + ' WHERE 1 = 1 ' + @Where + ') AS t1 
      WHERE t1.rn BETWEEN @paramStartRow + 1 
      AND @paramStartRow + @paramMaxRows ORDER BY rn;'

  EXEC sp_executesql @sql,
    N'@SchemaId INT,@TypeDesc NVARCHAR(60),@paramStartRow INT,@paramMaxRows INT',
    @SchemaID, @TypeDesc, @paramStartRow, @paramMaxRows;
END
GO

Now you can call it like this:

DECLARE @x dbo.VarcharParameters;

INSERT @x VALUES
  ('paramFoo',   'wuzzuh'),
  ('paramGamma', 'foobar');

DECLARE @y dbo.BitParameters;

INSERT @y VALUES
  ('paramIsView',  0),
  ('paramIsTable', 0);

EXEC dbo.ObviouslyAnonymizedProcedure2 
  @SchemaId      = 1, 
  @TypeDesc      = NULL, 
  @VCParams      = @x, 
  @BitParams     = @y, 
  @paramStartRow = 1, 
  @ParamMaxRows  = 20;

I won't show my results, because they'll be different from yours, but I bet the massive reduction in parameters will eliminate the compilation problems you have.

Also this is how you call this procedure from T-SQL; in order to call it from, say, C#, you'll need to use a DataTable or List or something compatible. I have an example here.

This is also much more flexible in terms of adding new parameters - you don't have to change the interface to the stored procedure, just add them to the procedure body (where relevant) and to the code that populates the data table.

Now just fill us in on what all the varchar parameters do and you might be one step closer to a solution. :-)

You can use X-Query for that:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE PROCEDURE dbo.ParseXML
   @XML XML
AS
BEGIN

  SELECT 
    Book.value('@id','NVARCHAR(100)') AS id,
    Book.value('author[1]','NVARCHAR(100)') AS author,
    Book.value('title[1]','NVARCHAR(100)') AS title,
    Book.value('price[1]','DECIMAL(10,4)') AS price,
    Book.value('publish_date[1]','DATE') AS publish_date

    FROM @XML.nodes('catalog/book')Catalog(Book)

RETURN;
END;

Query 1:

DECLARE @FileXML XML = '<?xml version="1.0"?>
<catalog>
   <book id="bk101">
      <author>Gambardella, Matthew</author>
      <title>XML Developer''s Guide</title>
      <genre>Computer</genre>
      <price>44.95</price>
      <publish_date>2000-10-01</publish_date>
      <description>An in-depth look at creating applications 
      with XML.</description>
   </book>
   <book id="bk102">
      <author>Ralls, Kim</author>
      <title>Midnight Rain</title>
      <genre>Fantasy</genre>
      <price>5.95</price>
      <publish_date>2000-12-16</publish_date>
      <description>A former architect battles corporate zombies, 
      an evil sorceress, and her own childhood to become queen 
      of the world.</description>
   </book>
</catalog>';

EXEC dbo.ParseXML @FileXML;

Results:

|    ID |               AUTHOR |                 TITLE | PRICE | PUBLISH_DATE |
|-------|----------------------|-----------------------|-------|--------------|
| bk101 | Gambardella, Matthew | XML Developer's Guide | 44.95 |   2000-10-01 |
| bk102 |           Ralls, Kim |         Midnight Rain |  5.95 |   2000-12-16 |

This shows you how to get to the values. From there you can easily insert them into your table.