I have a query with 17 separate, optional, parameters. I have declared each parameter = NULL so that I can test for NULL in the case that the user didn’t not pass in the parameter.
I am new enough to SQL Server that I am having difficulty building the WHERE clause with all of these optional parameters.
One solution I was advised on by a well paid SQL programmer, was to use a string in the stored proc and dynamically build the WHERE clause and exec it at the end of the sp. But the whole point of a stored proc is that it can be compiled and cached to make it faster, yet the string approach makes it have to compile every time it’s run! Not a good solution, but maybe it’s the best I can do . . .
I have tried many different approaches using different functions, etc. but I’ve hit a brick wall. Any help in sorting it out with YOUR techniques would be greatly appreciated:
1. To add the parameter to the WHERE clause and test for NULL I’ve used the COALESCE function such as “WHERE table.fieldname = COALESCE(@.Param, table.fieldname)”. This works well if there is only one item in the parameter, but in the case that I pass multiple items to the parameter, it completely fails.
2. To handle multiple items, for example, if @.Param = ‘3,7,98’ (essentially, a csv separated list of keys)
Code Snippet
WHERE table.fieldname IN(COALESCE(@.Param, table.fieldname))doesn’t work because @.Param needs to be parsed from a string into an array of integers in the parameter. So, I am using a UDF I discovered to parse the multi-item parameter. The UDF can be found at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlmag01/html/TreatYourself.asp and it returns a table variable that can be used in an IN statement. So I’m using
Code Snippet
ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@.Param,’,’))which works brilliantly in my WHERE statement AS LONG AS @.Param ISN’T NULL. So how do I test for NULL first and still use this approach to multi-item parameters?
I’ve tried
Code Snippet
WHERE @.Param IS NULL OR ISNULL(table.fieldname, 0) IN (SELECT value FROM dbo.fn_Split(@.Param,’,’))and though it works, the OR causes it to slow way down as it compares every record for the OR. (It slows down by approximately 800%.) The other thing I tried was
Code Snippet
ISNULL (table.fieldname, 0) IN (CASE WHEN @.Param IS NULL THEN ISNULL(table.fieldname, 0) ELSE (SELECT value FROM dbo.fn_Split(@.Param,’,’)))This fails with “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression” due to the multiple values in the parameter. (I can’t understand why the line without the CASE statement works, but the CASE line doesn’t!)
Am I even on the right track, cuz this is driving me mad and I just need a way to deal with optional multi-item parameters in an IN statement? HELP!
First of all, I think we need more background information...
-What is the end goal of the query?
-How are users interacting with the application? (reporting services, asp.net, etc.)
-What is the database structure like? How many tables are being joined, Approx size of tables
-Are all 17 parameters needed? Perhaps you could develop a set of linked reports that give the user more detailed information as they drill deeper.
As far as performance, I wouldn't worry too much about having the query compiled every time, this really doesn't take that long. Query performance in most cases comes from proper indexing.
|||Anthony,
Thank you for your response!
Here's the info:
- I migrated an Access Jet database to SQL but still use Access as the client.
- Query is the source for an Access report that is the most comprehensive report in the program. The user has sorting and grouping options in the report that allow them to view most everything they need from one report.
- The database structure is very good, and the tables aren't huge, the average size of a table is 15k records, but this query touches approximately 1/4 of all tables in the db. 20 tables are being joined and the largest table of the 20 has 28k records.
- This query is inserting all the values into a temp table which I then run my report queries on. Here's ALL the code with some of the WHERE clause: (I've put a couple of different multi-item parameter techniques in the WHERE clause- of course, neither of them work.)
Code Snippet
ALTER PROCEDURE [dbo].[spAG_ProjectStatusTESTFunction]
-- Add the parameters for this sp here
@.AGProjectKey varchar(255) = NULL,
@.OpenClosed bit = NULL,
@.ClientKey varchar(200) = NULL,
@.ProdCoKey varchar(255) = NULL,
@.ProjectTitle varchar(100) = NULL,
@.AgentKey varchar(50) = NULL,
@.GenreKey varchar(200) = NULL,
@.ProjectTypeKey varchar(255) = NULL,
@.ProjectType2Key varchar(255) = NULL,
@.DirectorKey varchar(255) = NULL,
@.ProdKey varchar(255) = NULL,
@.ExecProdKey varchar(255) = NULL,
@.ContactKey varchar(255) = NULL,
@.ProjectStatus varchar(50) = NULL,
@.Distributor varchar(50) = NULL,
@.MusSup varchar(255) = NULL,
@.NonClientFinalOut varchar(255) = NULL
AS
BEGIN
DECLARE @.sSQL varchar(8000);
DECLARE @.sWHERE varchar(8000);
DECLARE @.sORDERBY varchar(8000);
CREATE TABLE #rAG_ProjectStatusPre
(
[CoProdCompany] varchar(255) NULL, [ProductionCompanyDesc] varchar(500) NULL,
[AGProjectKey] int NOT NULL, [ProjectTitle] nvarchar(100) NULL, [ProdOfficeAddr1] nvarchar(50) NULL,
[ProdOfficeAddr2] nvarchar(50) NULL, [ProdOfficeAddr3] nvarchar(50) NULL, [ProdOfficeCity] nvarchar(15) NULL,
[ProdOfficeState] nvarchar(2) NULL, [ProdOfficeFax] nvarchar(20) NULL, [ProdOfficePhone] nvarchar(20) NULL,
[ProdOfficeZip] nvarchar(9) NULL, [ClientKey] int NULL, [FirstName] nvarchar(15) NULL, [LastName] nvarchar(20) NULL,
[AgentKey] int NULL, [AgentName] nvarchar(30) NULL, [Producer] nvarchar(30) NULL,
[ExecProducer] nvarchar(30) NULL, [Director] nvarchar(30) NULL, [Contact] nvarchar(30) NULL,
[Cast] nvarchar(30) NULL, [Source] nvarchar(50) NULL, [ProjectTypeDesc] nvarchar(50) NULL,
[ProjectType2Desc] nvarchar(50) NULL, [GenreDesc] nvarchar(50) NULL, [Network] nvarchar(50) NULL,
[ProductionStatusDesc] nvarchar(50) NULL, [ClosedProject] bit NULL, [ClosedDate] datetime NULL,
[MusicSupervisor] nvarchar(255) NULL, [Location] nvarchar(255) NULL, [ProdStartDate] nvarchar(50) NULL,
[WrapShootDate] nvarchar(50) NULL, [SpottingDate] nvarchar(50) NULL, [RecordingDate] nvarchar(50) NULL,
[MixingDate] nvarchar(50) NULL, [DeliveryDate] nvarchar(50) NULL, [ReleaseDate] nvarchar(50) NULL,
[FinalDub] nvarchar(50) NULL, [FilmTVBudget] nvarchar(50) NULL, [MusicBudget] nvarchar(50) NULL,
[Synopsis] nvarchar(255) NULL, [ProjectStatus] nvarchar(255) NULL, [NonClientFinalOut] nvarchar(255) NULL
);
BEGIN
SET @.sORDERBY = '';
SET @.sWHERE = '';
SET @.sSQL = '';
INSERT INTO #rAG_ProjectStatusPre (CoProdCompany, ProductionCompanyDesc, AGProjectKey, ProjectTitle,
ProdOfficeAddr1, ProdOfficeAddr2, ProdOfficeAddr3, ProdOfficeCity, ProdOfficeState, ProdOfficeZip, ProdOfficeFax, ProdOfficePhone,
ClientKey, [FirstName], [LastName], AgentKey, AgentName, Producer, ExecProducer, Director, Contact, [Cast], Source,
ProjectTypeDesc, ProjectType2Desc, GenreDesc, Network, ProductionStatusDesc, ClosedProject, ClosedDate, MusicSupervisor,
Location, ProdStartDate, WrapShootDate, SpottingDate, RecordingDate, MixingDate, DeliveryDate,
ReleaseDate, FinalDub, FilmTVBudget, MusicBudget, Synopsis, ProjectStatus, NonClientFinalOut)
SELECT tProductionCompanies_1.ProductionCompanyDesc AS CoProdCompany,
tProductionCompanies.ProductionCompanyDesc, tAGProjects.AGProjectKey,
tAGProjects.ProjectTitle, tAGProjects.ProdOfficeAddr1, tAGProjects.ProdOfficeAddr2,
tAGProjects.ProdOfficeAddr3, tAGProjects.ProdOfficeCity, tAGProjects.ProdOfficeState,
tAGProjects.ProdOfficeZip, tAGProjects.ProdOfficeFax, tAGProjects.ProdOfficePhone,
tClients.ClientNumber AS [ClientKey], tClients.FirstName AS [FirstName],
tClients.LastName AS [LastName], tAgents.AgentKey, tAgents.AgentName,
[tMailList].[FirstName] + ' ' + [tMailList].[LastName] AS Producer,
[tMailList_1].[FirstName] + ' ' + [tMailList_1].[LastName] AS ExecProducer,
[tMailList_2].[FirstName] + ' ' + [tMailList_2].[LastName] AS Director,
[tMailList_3].[FirstName] + ' ' + [tMailList_3].[LastName] AS Contact,
[tMailList_4].[FirstName] + ' ' + [tMailList_4].[LastName] AS [Cast],
tProjectSources.Source, tProjectTypes.ProjectTypeDesc, tProjectType2s.ProjectType2Desc,
tGenre.GenreDesc, tAGProjects.Network, tProductionStatus.ProductionStatusDesc, tAGProjects.ClosedProject,
tAGProjects.ClosedDate, tAGProjects.MusicSupervisor, tAGProjects.Location, tAGProjects.ProdStartDate,
tAGProjects.WrapShootDate, tAGProjects.SpottingDate, tAGProjects.RecordingDate,
tAGProjects.MixingDate, tAGProjects.DeliveryDate, tAGProjects.ReleaseDate, tAGProjects.FinalDub,
tAGProjects.FilmTVBudget, tAGProjects.MusicBudget, tAGProjects.Synopsis,
tAGProjects.ProjectStatus, tAGProjects.NonClientFinalOut
FROM (tMailList RIGHT JOIN (tClients RIGHT JOIN ((((((((((((((tAGProjects LEFT JOIN tProductionCompanies ON
tAGProjects.ProductionCompanyKey = tProductionCompanies.ProductionCompanyKey) LEFT JOIN
tProductionCompanies AS tProductionCompanies_1 ON tAGProjects.CoProductionCompanyKey =
tProductionCompanies_1.ProductionCompanyKey) LEFT JOIN tProjectTypes ON tAGProjects.ProjectTypeKey =
tProjectTypes.ProjectTypeKey) LEFT JOIN tProjectType2s ON tAGProjects.Type2Key = tProjectType2s.ProjectType2Key)
LEFT JOIN tGenre ON tAGProjects.GenreKey = tGenre.GenreKey) LEFT JOIN tProductionStatus ON
tAGProjects.ProductionStatusKey = tProductionStatus.ProductionStatusKey) LEFT JOIN
(tProjectAgents LEFT JOIN tAgents ON tProjectAgents.AgentKey = tAgents.AgentKey) ON
tAGProjects.AGProjectKey = tProjectAgents.AGProjectKey) LEFT JOIN (tProjectCast LEFT JOIN
tMailList AS tMailList_4 ON tProjectCast.ContactKey = tMailList_4.MailListKey) ON
tAGProjects.AGProjectKey = tProjectCast.AGProjectKey) LEFT JOIN tProjectClients ON
tAGProjects.AGProjectKey = tProjectClients.AGProjectKey) LEFT JOIN (tProjectContacts LEFT JOIN
tMailList AS tMailList_3 ON tProjectContacts.ContactKey = tMailList_3.MailListKey) ON
tAGProjects.AGProjectKey = tProjectContacts.AGProjectKey) LEFT JOIN (tProjectDirectors LEFT JOIN
tMailList AS tMailList_2 ON tProjectDirectors.ContactKey = tMailList_2.MailListKey) ON
tAGProjects.AGProjectKey = tProjectDirectors.AGProjectKey) LEFT JOIN (tProjectExecProducers LEFT JOIN
tMailList AS tMailList_1 ON tProjectExecProducers.ContactKey = tMailList_1.MailListKey) ON
tAGProjects.AGProjectKey = tProjectExecProducers.AGProjectKey) LEFT JOIN tProjectProducers ON
tAGProjects.AGProjectKey = tProjectProducers.AGProjectKey) LEFT JOIN tProjectSources ON
tAGProjects.AGProjectKey = tProjectSources.AGProjectKey) ON tClients.ClientNumber = tProjectClients.ClientKey) ON tMailList.MailListKey = tProjectProducers.ContactKey)
WHERE tAGProjects.ClosedProject = COALESCE(@.OpenClosed, tAGProjects.ClosedProject)
AND @.AGProjectKey IS NULL OR ISNULL(tAGProjects.AGProjectKey, 0) IN (SELECT Value FROM dbo.fn_Split(@.AGProjectKey,','))
AND tAGProjects.ProjectTitle LIKE COALESCE(@.ProjectTitle, tAGProjects.ProjectTitle)
AND ISNULL(tClients.ClientNumber, 0) IN(CASE WHEN @.ClientKey IS NULL THEN ISNULL(tClients.ClientNumber, 0) ELSE @.ClientKey END)
END
BEGIN
SELECT DISTINCT AGProjectKey, ProjectTitle, FirstName, ClientKey, LastName, ProdOfficeAddr1, ProdOfficeAddr2, ProdOfficeAddr3, ProdOfficeCity, ProdOfficeState, ProdOfficeZip, ProdOfficeFax, ProdOfficePhone,
Network, ClosedProject, ClosedDate, MusicSupervisor, Location, ProdStartDate, WrapShootDate, SpottingDate, RecordingDate, MixingDate, DeliveryDate,
ReleaseDate, FinalDub, FilmTVBudget, MusicBudget, Synopsis, ProjectStatus, NonClientFinalOut, ProjectTypeDesc, ProjectType2Desc,
GenreDesc, ProductionCompanyDesc, CoProdCompany, ProductionStatusDesc
FROM #rAG_ProjectStatusPre
END
DROP TABLE #rAG_ProjectStatusPre
END
|||Try:
case
when (@.Param is null) or (table.fieldname is null) then 1
else case when exists(select * from dbo.fn_split(@.Param, ',') as t where t.value = table.fieldname) then 1 else 0 end
end = 1
Not sure it will give good performance.
Here is a very good article about this theme.
Dynamic Search Conditions in
AMB
|||Okay, thanks for the follow-up information.
The previous poster's code will work, but in my opinion it makes the code rather confusing for future developers. I would code it to dynamically build the where clause.
As the table get larger performance will almost certainly become an issue due to the large number of joins. Make sure that you at least index the larger tables on the join columns and if possible create a 'covered index'. If you are unaware what that is you can learn more on books-online or try googling it.
You could code the where clause something like this...
declare @.where varchar(1000)
set @.where='where '
if @.AGProjectKey is not null
begin
set @.where=@.where + @.AGProject + ' and '
end
if @.ClientKey is not null
begin
set @.where=@.where + @.ClientKey + ' and '
end
Good luck. Let me know if you have any more questions.
No comments:
Post a Comment