SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 declare variable within Function returning a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jogrady
Starting Member

USA
2 Posts

Posted - 04/24/2013 :  12:02:47  Show Profile  Reply with Quote
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




Edited by - jogrady on 04/24/2013 12:19:59

djj55
Constraint Violating Yak Guru

USA
283 Posts

Posted - 04/24/2013 :  12:13:06  Show Profile  Reply with Quote
You should probably use “LIKE” instead of “=”

djj
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4360 Posts

Posted - 04/24/2013 :  12:16:25  Show Profile  Reply with Quote
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/

Edited by - Lamprey on 04/24/2013 12:18:16
Go to Top of Page

jogrady
Starting Member

USA
2 Posts

Posted - 04/24/2013 :  13:15:06  Show Profile  Reply with Quote
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!

Edited by - jogrady on 04/24/2013 13:57:00
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000