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 |
jogrady
Starting Member
2 Posts |
Posted - 2013-04-24 : 12:02:47
|
I have the following function which determines which values are being used as filtering criteria. However, I am trying to add logic to determine whether wildcard chars need to be added to the variables passed into the function. Declaring variables for each parameter and detappears to be the correct way to go, but I'm experiencing syntatical issues when I attempt to compile this:CREATE FUNCTION dbo.GET_CUSTBILL_HDR_RECS_FCTN (@RecordType Char(1) = 'R', @AcctNbr varchar(15) = NULL, @CustNam varchar(30) = NULL, @HseNbr varchar(5) = NULL, @StNamApt varchar(30) = NULL)RETURNS TABLEASbegin declare @acctnum string select distinct RECORD_TYPE ,ACCT_NBR ,DIST_NBR ,CUST_NAM ,HSE_NBR ,ST_NAM_APTfrom CUST_BILL where upper(RECORD_TYPE) = upper(@RecordType) AND ACCT_NBR = case when @AcctNbr IS NULL or LTRIM(RTRIM(@AcctNbr)) = 'NULL' then ACCT_NBR else '%' + LTRIM(RTRIM(@AcctNbr)) + '%' end AND upper(CUST_NAM) = case when @CustNam IS NULL or LTRIM(RTRIM(@CustNam)) = 'NULL' then CUST_NAM else '%' + LTRIM(RTRIM(@CustNam)) + '%' end AND HSE_NBR = case when @HseNbr IS NULL or LTRIM(RTRIM(@HseNbr)) = 'NULL' then HSE_NBR else '%' + LTRIM(RTRIM(@HseNbr)) + '%' end AND upper(ST_NAM_APT) = case when @StNamApt IS NULL or LTRIM(RTRIM(@StNamApt)) = 'NULL' then ST_NAM_APT else '%' + LTRIM(RTRIM(@StNamApt)) + '%' end RETURN end |
|
djj55
Constraint Violating Yak Guru
352 Posts |
Posted - 2013-04-24 : 12:13:06
|
You should probably use “LIKE” instead of “=”djj |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-04-24 : 12:16:25
|
What errors are you geting?Also, by default SQL is case insensitive, so using the UPPER function probably isn't needed. At any rate, I think you need to drop the CASE expressions and use logic. AS a pattern, replace:AND ACCT_NBR = case when ISNULL(@AcctNbr) or LTRIM(RTRIM(@AcctNbr)) = 'NULL' then ACCT_NBR else '%' + LTRIM(RTRIM(@AcctNbr)) + '%' end with AND( @AcctNbr IS NULL OR LTRIM(RTRIM(@AcctNbr)) = 'NULL' OR ACCT_NBR LIKE '%' + LTRIM(RTRIM(@AcctNbr)) + '%' ) Additionally, this (catch-all query) is going to perform poorly becuase your predicates are not sargable. Here is an article about performance and some options:http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/ |
|
|
jogrady
Starting Member
2 Posts |
Posted - 2013-04-24 : 13:15:06
|
Great feedback! Thank you. After reading the hyperlink article, I think I'm going to use the hint option and avoid declaring variables. If I re-work the where clause as you suggest: @AcctNbr IS NULL OR LTRIM(RTRIM(@AcctNbr)) = 'NULL'only checks the input parameter for null value or the word 'NULL' and the way I wrote the code it will set the column value comparison back to itself if either one of those conditions exist. I don't believe your suggestion would render the expected result that I am looking for.This is the final product which includes a Coalesceselect distinct RECORD_TYPE, ACCT_NBR, DIST_NBR, CUST_NAM, HSE_NBR, ST_NAM_APT from NES_CUST_BILL where RECORD_TYPE = COALESCE(@RecordType, @RecordType) AND ACCT_NBR like case when @AcctNbr IS NULL or LTRIM(RTRIM(@AcctNbr)) = 'NULL' then ACCT_NBR else '%' + LTRIM(RTRIM(@AcctNbr)) + '%' end AND CUST_NAM like case when @CustNam IS NULL or LTRIM(RTRIM(@CustNam)) = 'NULL' then CUST_NAM else '%' + LTRIM(RTRIM(@CustNam)) + '%' end AND HSE_NBR like case when @HseNbr IS NULL or LTRIM(RTRIM(@HseNbr)) = 'NULL' then HSE_NBR else '%' + LTRIM(RTRIM(@HseNbr)) + '%' end AND ST_NAM_APT like case when @StNamApt IS NULL or LTRIM(RTRIM(@StNamApt)) = 'NULL' then ST_NAM_APT Thanks again! |
|
|
|
|
|
|
|