You need to split your parameters into rows and use where ... in
as a check.
Using a split function like this one your code could look like this.
CREATE PROCEDURE [dbo].[sp_tst_CSENG_JulieCapitalHours]
@StartDate DATETIME ,
@EndDate DATETIME ,
@ProjHomeGrp NVARCHAR(MAX) ,
@ProjHier NVARCHAR(MAX)
AS
BEGIN
DECLARE @ProjHomeGrpTbl TABLE (Value NVARCHAR(4))
INSERT INTO @ProjHomeGrpTbl(Value)
SELECT LTRIM(RTRIM(s))
FROM dbo.Split(',', @ProjHomeGrp);
DECLARE @ProjHierTbl TABLE (Value NVARCHAR(5))
INSERT INTO @ProjHierTbl(Value)
SELECT LTRIM(RTRIM(s))
FROM dbo.Split(',', @ProjHier);
SELECT [Capital Project] ,
[Capital Task] ,
ResourceName ,
ProjectName ,
[Project Home Group] ,
ActualWork ,
TimeByDay ,
ResourceStandardRate ,
ActualWork * ResourceStandardRate AS Dollars ,
[Project Hierarchy]
FROM [IR.CapOnly]
WHERE ( TimeByDay >= @StartDate )
AND ( [Project Home Group] in (SELECT Value FROM @ProjHomeGrpTbl) )
AND ( TimeByDay <= @EndDate )
AND ( ActualWork > 0 )
AND ( [Project Hierarchy] in (SELECT Value FROM @ProjHierTbl) )
ORDER BY ProjectName ,
ResourceName
END
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. :-)
Best Answer
No, it caches all versions. Or rather, it caches one version with all paths explored, compiled with the first set of passed in variables. Cardinality estimation for all plans will be done using them. This can be extra bad if some passed in values are NULL.
Here's a quick demo, using the Stack Overflow database.
Create an index:
Create a stored procedure with an index hint that points to an index that doesn't exist, in branched code.
If I execute that stored proc looking for Reputation = 1, I get an error.
If we fix the index name and re-run the query, the cached plan looks like this:
Inside, the XML will have two references to the
@Reputation
variable.A slightly simpler test would be to just get an estimated plan for the stored proc. You can see the optimizer exploring both paths:
No, it will retain the runtime value of the first compilation.
If we re-execute with a different
@Reputation
:From the actual plan:
We still have a compiled value of 1, but now a runtime value of 2.
In the plan cache, which you can check out with a free tool like the one my company develops, sp_BlitzCache:
The stored procedure has been called twice, and each statement in it has been called once.
So what do we have? One cached plan for both queries in the stored procedure.
If you want this sort of branched logic, you'd have to call sub-stored procedures:
Or dynamic SQL:
Hope this helps!