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. :-)
I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:
WHILE (@@FETCH_STATUS = 0) AND
( @About80MoreBooleanExpressionsHere)
BEGIN /* 1 */
-- about 700 lines of logic, math and if-parameter-this-then-that
-- stuff omitted
INSERT InnerProcedureCallWithinLoop__TraceTable
VALUES (@from_locn, @About53PARAMSOMITTED
EXEC @ConsiderItem =
InnerProcedureCallWithinLoop
@from_locn,
@About53PARAMSOMITTED,
...
If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.
ALTER PROC InnerProcedureCallWithinLoop
@from_locn int,
@About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc
This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions). If that is your problem, you need to look at CLR or email methods to externalise the captured output.
Best Answer
If it does not take any parameters in or out then
Or when using the sqlplus interface EXEC DISABLE_TRIGGERS;
I have to hope that this is not production code as a procedure that disables all the triggers could have many consequences. This code as originally posted should be more comprehensive if used in production, particularly for a migration. Triggers do things, whether you agree with their use or not, they can enforce business logic and stitch together poorly implemented business logic.
You need to know when the triggers were disabled and re enabled and that all transactions stopped at the start of the migration and only resumed after the migration. Before, during and after the migration you need a log of all the actions that were taken so you can improve the process.
A better way to do this would be to add a parameter: 0 or 1 to disable or enable the triggers
something like