Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
evanburen
Posting Yak Master
167 Posts |
 Posted - 2010-06-02 : 15:43:39
|
| I just want to search on the field for which there is a value passed to it but the first line of my case statement throws an error. CREATE PROCEDURE [dbo].[p_Industries]( @GICS_Sector VarChar(50) = NULL, @GICS_Group VarChar(50) = NULL, @GICS_Industry VarChar(50) = NULL, @GICS_SubIndustry VarChar(50) = NULL)ASSELECT Companies.GICS_Sector,Companies.GICS_Group,Companies.GICS_Industry, Companies.GICS_SubIndustry,FROM companyFinancialData WHERE CASE WHEN @GICS_Sector IS NOT NULL THEN GICS_Sector = @GICS_Sector WHEN @GICS_Group IS NOT NULL THEN GICS_Group = @GICS_Group WHEN @GICS_Industry IS NOT NULL THEN GICS_Industry = @GICS_Industry WHEN @GICS_SubIndustry IS NOT NULL THEN GICS_SubIndustry = @GICS_SubIndustryEND |
|
|
mandm
Posting Yak Master
120 Posts |
Posted - 2010-06-02 : 17:52:04
|
| This is how to handle optional parameters. The proper case statement syntax gets pretty clunky.CREATE PROCEDURE [dbo].[p_Industries] ( @GICS_Sector VarChar(50) = NULL, @GICS_Group VarChar(50) = NULL, @GICS_Industry VarChar(50) = NULL, @GICS_SubIndustry VarChar(50) = NULL ) AS SELECT Companies.GICS_Sector, Companies.GICS_Group, Companies.GICS_Industry, Companies.GICS_SubIndustry, FROM companyFinancialData WHERE (@GICS_Sector IS NULL OR GICS_Sector = @GICS_Sector) AND (@GICS_Group IS NULL OR GICS_Group = @GICS_Group) AND (@GICS_Industry IS NULL OR GICS_Industry = @GICS_Industry) AND (@GICS_SubIndustry IS NULL OR GICS_SubIndustry = @GICS_SubIndustry)FYI the proper case statement syntax would be WHERE GICS_Sector = CASE WHEN @GICS_Sector IS NULL THEN GICS_Sector ELSE @GICS_Sector END |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-06-02 : 19:56:53
|
| If you typically only pass in a subset of the parameters (e.g. One), then consider adding the "OPTION (RECOMPILE)" option to your query. It will look at the specific values of the parameters, recognize that most are Null and generate an execution plan tailored to that. In essence, it will throww out the other three predicates in the WHERE clause since it knows that the OR condition will evaluate to True for the three parameters that are Null. You can realize a significant performance gain.If you are typically passing in most or all of the parameters, the recompile will only be delaying the query.=======================================A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007) |
|
|
|
|
|
|
|
|
Optional Parameters