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. :-)
Just working from Paul's comment. The key difference between the statement in Books Online and your interpretation:
It says: ... one row per cached stored procedure plan.
You read: ... one row per cached stored procedure.
You can check which plan attributes are different and thus leading to different copies of the plan by looking at the contents of the attributes DMV, ordering by attribute name. You should see at least one attribute where two rows for the same text have different values in the value
column.
SELECT t.[text], pa.attribute, pa.value
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS pa
WHERE LOWER(t.[text]) LIKE N'%create%procedure%procedure_name%'
AND LOWER(t.[text]) NOT LIKE N'%sys.dm%'
AND pa.is_cache_key = 1
ORDER BY t.[text], pa.attribute;
Typically, you will see differences in one of these two attributes:
set_options
This is due to runtime differences in settings like ARITHABORT
, QUOTED_IDENTIFIER
, ANSI_NULLS
, etc. I talk about this in the following post:
user_id
This isn't really the user running the query, but rather it is due to two different users having different default schemas (the value is actually schema_id
), and at least one of them is calling the stored procedure without the schema prefix (SQL Server caches a different plan because the search path is different - it must check the default schema first). I talk about this in the above post as well as the following:
(Also, be sure you are looking at the overarching plan for the procedure, not two plans for two completely different statements within the stored procedure, e.g. WHERE p.objtype = N'Proc'
.)
Best Answer
There are several ways to get this that require different input information
INFORMATION_SCHEMA:
You only need to know the name of the object you want the definition for.
Note, this schema is database specific, so you need to be in the context of the database where the procedure exists for this query to return the correct data. Also note, it only returns the first 4000 characters of the procedure.
OBJECT_DEFINITION:
You need to know the object_id of the procedure you're looking for.
Database-specific as well, but returns the complete text of the object.
sys.sql_modules:
You need to know the object_id of the procedure you're looking for.
Database-specific as well, but returns the complete text of the object.