declare variable within Function returning a table

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

declare variable within Function returning a table

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 TABLE
AS
begin

declare @acctnum string

select distinct
RECORD_TYPE
,ACCT_NBR
,DIST_NBR
,CUST_NAM
,HSE_NBR
,ST_NAM_APT
from
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 Coalesce
select 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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources