Issue in SQL function to handle search form submission

hi,
i built a search form in a web application as shown in the image below

My search pageNow a user of this page is expected to enter values into one or many of the 6 search fields or may do an open search leaving all fields blank.

I have the below function fetching results.

USE [KingsBayY]
GO
/****** Object:  UserDefinedFunction [dbo].[test_fn_transaction_search]    Script Date: 03/18/2015 00:12:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[test_fn_transaction_search](@receiptnum varchar(4001) ='',--00040401010000021
												@account varchar(4001) ='',
												@FN varchar(4001) ='',
												@ln varchar(4001) ='',--DateTime = '',
												@dt varchar(4001) ='',
												@program varchar(4001) ='',--)
												@attendee varchar(4001) ='')
												--stdt
												--enddt
						
RETURNS @resulttable TABLE
   (trid  int,
    trdate   varchar(4001), --COLLATE database_default
    tramount       money,
    trinvoice  varchar(4001),
    trcashierid varchar(4001),
    trvoided varchar(4001),
    trrecallid varchar(4001),
    trrecalltype varchar(4001),
    trattendee varchar(4001),
    trperiod varchar(4001),
    stopbilling varchar(4001),
    caccount varchar(4001),
    firstname varchar(4001),
    lastname varchar(4001),
    trprogram varchar(4001),
    itemlookupcode varchar(4001),
    paydate varchar(4001)
    )
AS


BEGIN
IF (@receiptnum='' and @account='' and @FN=''and @ln='' and @dt='' and @program='' and @attendee='')
	INSERT INTO @resulttable
	select tr.ID AS [Transaction ID], tr.Time AS [trDt], tr.Total AS [amount], 
			tr.ReceiptNumber AS [invoice], tr.CashierID AS [cashid], 
			tr.Voided AS [voided], tr.RecallID AS [recallid], tr.RecallType AS [recalltype], 
			tr.Comment AS [attendee], tr.ReferenceNumber AS [trperiod], tr.StopBilling  AS [stopbilling],
			--fields from customer
			c.AccountNumber AS [accountnum], c.FirstName AS [firstname], c.LastName AS [lastname],
			--fields from item
			it.Description AS [programname], it.ItemLookupCode AS [trItemLookupCode],
			--fields from tenderentry
			tent.PaymentDate AS [paydate]
		 
	 from "Transaction" tr, TransactionEntry trent, Item it, Customer c, TenderEntry tent
	 where trent.TransactionID = tr.ID
	 and tent.TransactionID = tr.ID
	 and trent.ItemID = it.ID
	 and c.ID=tr.CustomerID
	 and tr.RecallID =0 --parent
	 --and tr.RecallType=0 --parent transactions and independant sales
			 
	 order by tr.id, tr.time;
else IF (@receiptnum<>'' or @account<>'' or @FN<>'' or @ln<>'' or @dt<>'' or @program<>'' or @attendee<>'')
    INSERT INTO @resulttable
	select tr.ID AS [Transaction ID], tr.Time AS [trDt], tr.Total AS [amount], 
			tr.ReceiptNumber AS [invoice], tr.CashierID AS [cashid], 
			tr.Voided AS [voided], tr.RecallID AS [recallid], tr.RecallType AS [recalltype], 
			tr.Comment AS [attendee], tr.ReferenceNumber AS [trperiod], tr.StopBilling  AS [stopbilling],
			--fields from customer
			c.AccountNumber AS [accountnum], c.FirstName AS [firstname], c.LastName AS [lastname],
			--fields from item
			it.Description AS [programname], it.ItemLookupCode AS [trItemLookupCode],
			--fields from tenderentry
			tent.PaymentDate AS [paydate]
		 
	 from "Transaction" tr, TransactionEntry trent, Item it, Customer c, TenderEntry tent
	 where trent.TransactionID = tr.ID
	 and tent.TransactionID = tr.ID
	 and trent.ItemID = it.ID
	 and c.ID=tr.CustomerID
	 and tr.RecallID =0 --parent transactions and independant sales
	 --and tr.RecallType=0 --
	 and(
	 tr.ReceiptNumber=ISNULL(@receiptnum,tr.ReceiptNumber)--invoice
	 or c.accountnumber=ISNULL(@account,c.accountnumber)--account
	 or c.FirstName=ISNULL(@FN, c.FirstName)
	 or c.LastName=ISNULL(@ln, c.LastName)--customer
	 or tr.time=ISNULL(@dt,tr.time)--date
	 or tr.Comment=ISNULL(@attendee,tr.Comment)
	 --program
	 )
	 union
	 select tr.ID AS [Transaction ID], tr.Time AS [trDt], tr.Total AS [amount], 
			tr.ReceiptNumber AS [invoice], tr.CashierID AS [cashid], 
			tr.Voided AS [voided], tr.RecallID AS [recallid], tr.RecallType AS [recalltype], 
			tr.Comment AS [attendee], tr.ReferenceNumber AS [trperiod], tr.StopBilling  AS [stopbilling],
			--fields from customer
			c.AccountNumber AS [accountnum], c.FirstName AS [firstname], c.LastName AS [lastname],
			--fields from item
			it.Description AS [programname], it.ItemLookupCode AS [trItemLookupCode],
			--fields from tenderentry
			tent.PaymentDate AS [paydate]
		 
	 from "Transaction" tr, TransactionEntry trent, Item it, Customer c, TenderEntry tent
	 where trent.TransactionID = tr.ID
	 and tent.TransactionID = tr.ID
	 and trent.ItemID = it.ID
	 and c.ID=tr.CustomerID
	 and tr.RecallID =0 --parent transactions and independant sales
	 --and tr.RecallType=0 --
	 and(
	 ISNULL(tr.ReceiptNumber,'1')= ISNULL(@receiptnum,'1')--invoice
	 and ISNULL(c.accountnumber,'1')= ISNULL(@account,'1')--account
	 and ISNULL(c.FirstName,'1')= ISNULL(@FN,'1')
	 and ISNULL(c.LastName,'1')= ISNULL(@ln,'1')--customer
	 and ISNULL(tr.time,'1')= ISNULL(@dt,'1')--date
	 and ISNULL(tr.Comment,'1')=ISNULL(@attendee,tr.Comment)
	 --program
	 )
	 order by tr.id, tr.time;
RETURN
END

Open in new window


I am getting wrong results from this procedure. The problems i notice are,

a) no matter how try to search for.. open search, with one search parameter specified.. with multiple search parameter values specified, the procedure returns all results. How can i edit it to return only results according to the search parameters used?

b)some of the search parameters may not be an exact match ..like Attendee field for example.. The user might enter 'John Doe' whereas the table has a attendee column with one of the cells value as 'John Doe - the genius'.. i want to be able to search for the term entered within cell values. How do i do this.
solarisinfosysAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Walter RitzelSenior Software EngineerCommented:
So, in order to make it work with less code possible is to use only the insert on the else part of your code.
Why, you may ask: because since it is using ISNULL for the fields, it will deal correctly with the fact that one of the fields could come filled or not.
To search inside the content of the field and find pieces, you need to use LIKE.

So, I believe your code should be something like this:
USE [KingsBayY]
GO
/****** Object:  UserDefinedFunction [dbo].[test_fn_transaction_search]    Script Date: 03/18/2015 00:12:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[test_fn_transaction_search](@receiptnum varchar(4001) ='',--00040401010000021
												@account varchar(4001) ='',
												@FN varchar(4001) ='',
												@ln varchar(4001) ='',--DateTime = '',
												@dt varchar(4001) ='',
												@program varchar(4001) ='',--)
												@attendee varchar(4001) ='')
												--stdt
												--enddt
						
RETURNS @resulttable TABLE
   (trid  int,
    trdate   varchar(4001), --COLLATE database_default
    tramount       money,
    trinvoice  varchar(4001),
    trcashierid varchar(4001),
    trvoided varchar(4001),
    trrecallid varchar(4001),
    trrecalltype varchar(4001),
    trattendee varchar(4001),
    trperiod varchar(4001),
    stopbilling varchar(4001),
    caccount varchar(4001),
    firstname varchar(4001),
    lastname varchar(4001),
    trprogram varchar(4001),
    itemlookupcode varchar(4001),
    paydate varchar(4001)
    )
AS


BEGIN
    INSERT INTO @resulttable
	select tr.ID AS [Transaction ID], tr.Time AS [trDt], tr.Total AS [amount], 
			tr.ReceiptNumber AS [invoice], tr.CashierID AS [cashid], 
			tr.Voided AS [voided], tr.RecallID AS [recallid], tr.RecallType AS [recalltype], 
			tr.Comment AS [attendee], tr.ReferenceNumber AS [trperiod], tr.StopBilling  AS [stopbilling],
			--fields from customer
			c.AccountNumber AS [accountnum], c.FirstName AS [firstname], c.LastName AS [lastname],
			--fields from item
			it.Description AS [programname], it.ItemLookupCode AS [trItemLookupCode],
			--fields from tenderentry
			tent.PaymentDate AS [paydate]
		 
	 from "Transaction" tr, TransactionEntry trent, Item it, Customer c, TenderEntry tent
	 where trent.TransactionID = tr.ID
	 and tent.TransactionID = tr.ID
	 and trent.ItemID = it.ID
	 and c.ID=tr.CustomerID
	 and tr.RecallID =0 --parent transactions and independant sales
	 --and tr.RecallType=0 --
	 and(
	 tr.ReceiptNumber=ISNULL(@receiptnum,tr.ReceiptNumber)--invoice
	 or c.accountnumber=ISNULL(@account,c.accountnumber)--account
	 or c.FirstName=ISNULL(@FN, c.FirstName)
	 or c.LastName=ISNULL(@ln, c.LastName)--customer
	 or tr.time=ISNULL(@dt,tr.time)--date
	 or tr.Comment Like '%' + ISNULL(@attendee,' ') + '%'
	 --program
	 )
	 order by tr.id, tr.time;
RETURN
END

Open in new window


I did not have compiled the code, so you may find some typing errors.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nishant joshiTechnology Development ConsultantCommented:
a) no matter how try to search for.. open search, with one search parameter specified.. with multiple search parameter values specified, the procedure returns all results. How can i edit it to return only results according to the search parameters used?

Change required in below code as you may receive null value from search.

Before :
IF (@receiptnum='' and @account='' and @FN=''and @ln='' and @dt='' and @program='' and @attendee='')

Open in new window


IF (@receiptnum<>'' or @account<>'' or @FN<>'' or @ln<>'' or @dt<>'' or @program<>'' or @attendee<>'')

Open in new window


After :

IF (ISNULL(@receiptnum,'')='' and ISNULL(@account,'')='' and ISNULL(@FN,'')=''and ISNULL(@ln,'')='' and ISNULL(@dt,'')='' and ISNULL(@program,'')='' and ISNULL(@attendee,'')='')

Open in new window

IF (ISNULL(@receiptnum,'')<>'' or ISNULL(@account,'')<>'' or ISNULL(@FN,'')<>'' or ISNULL(@ln,'')<>'' or ISNULL(@dt,'')<>'' or ISNULL(@program,'')<>'' or ISNULL(@attendee,'')<>'')

Open in new window



b)some of the search parameters may not be an exact match ..like Attendee field for example.. The user might enter 'John Doe' whereas the table has a attendee column with one of the cells value as 'John Doe - the genius'.. i want to be able to search for the term entered within cell values. How do i do this.

You can add like condition directly to field.

Not required UNION at its giving all result.

Final code would be :

USE [KingsBayY]
GO
/****** Object:  UserDefinedFunction [dbo].[test_fn_transaction_search]    Script Date: 03/18/2015 00:12:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[test_fn_transaction_search](@receiptnum varchar(4001) ='',--00040401010000021
												@account varchar(4001) ='',
												@FN varchar(4001) ='',
												@ln varchar(4001) ='',--DateTime = '',
												@dt varchar(4001) ='',
												@program varchar(4001) ='',--)
												@attendee varchar(4001) ='')
												--stdt
												--enddt
						
RETURNS @resulttable TABLE
   (trid  int,
    trdate   varchar(4001), --COLLATE database_default
    tramount       money,
    trinvoice  varchar(4001),
    trcashierid varchar(4001),
    trvoided varchar(4001),
    trrecallid varchar(4001),
    trrecalltype varchar(4001),
    trattendee varchar(4001),
    trperiod varchar(4001),
    stopbilling varchar(4001),
    caccount varchar(4001),
    firstname varchar(4001),
    lastname varchar(4001),
    trprogram varchar(4001),
    itemlookupcode varchar(4001),
    paydate varchar(4001)
    )
AS


BEGIN
IF (ISNULL(@receiptnum,'')='' and ISNULL(@account,'')='' and ISNULL(@FN,'')=''and ISNULL(@ln,'')='' and ISNULL(@dt,'')='' and ISNULL(@program,'')='' and ISNULL(@attendee,'')='')
	INSERT INTO @resulttable
	select tr.ID AS [Transaction ID], tr.Time AS [trDt], tr.Total AS [amount], 
			tr.ReceiptNumber AS [invoice], tr.CashierID AS [cashid], 
			tr.Voided AS [voided], tr.RecallID AS [recallid], tr.RecallType AS [recalltype], 
			tr.Comment AS [attendee], tr.ReferenceNumber AS [trperiod], tr.StopBilling  AS [stopbilling],
			--fields from customer
			c.AccountNumber AS [accountnum], c.FirstName AS [firstname], c.LastName AS [lastname],
			--fields from item
			it.Description AS [programname], it.ItemLookupCode AS [trItemLookupCode],
			--fields from tenderentry
			tent.PaymentDate AS [paydate]
		 
	 from "Transaction" tr, TransactionEntry trent, Item it, Customer c, TenderEntry tent
	 where trent.TransactionID = tr.ID
	 and tent.TransactionID = tr.ID
	 and trent.ItemID = it.ID
	 and c.ID=tr.CustomerID
	 and tr.RecallID =0 --parent
	 --and tr.RecallType=0 --parent transactions and independant sales
			 
	 order by tr.id, tr.time;
else IF (ISNULL(@receiptnum,'')<>'' or ISNULL(@account,'')<>'' or ISNULL(@FN,'')<>'' or ISNULL(@ln,'')<>'' or ISNULL(@dt,'')<>'' or ISNULL(@program,'')<>'' or ISNULL(@attendee,'')<>'')
    INSERT INTO @resulttable
	select tr.ID AS [Transaction ID], tr.Time AS [trDt], tr.Total AS [amount], 
			tr.ReceiptNumber AS [invoice], tr.CashierID AS [cashid], 
			tr.Voided AS [voided], tr.RecallID AS [recallid], tr.RecallType AS [recalltype], 
			tr.Comment AS [attendee], tr.ReferenceNumber AS [trperiod], tr.StopBilling  AS [stopbilling],
			--fields from customer
			c.AccountNumber AS [accountnum], c.FirstName AS [firstname], c.LastName AS [lastname],
			--fields from item
			it.Description AS [programname], it.ItemLookupCode AS [trItemLookupCode],
			--fields from tenderentry
			tent.PaymentDate AS [paydate]
		 
	 from "Transaction" tr, TransactionEntry trent, Item it, Customer c, TenderEntry tent
	 where trent.TransactionID = tr.ID
	 and tent.TransactionID = tr.ID
	 and trent.ItemID = it.ID
	 and c.ID=tr.CustomerID
	 and tr.RecallID =0 --parent transactions and independant sales
	 --and tr.RecallType=0 --
	 and(
	 tr.ReceiptNumber=ISNULL(@receiptnum,tr.ReceiptNumber)--invoice
	 AND c.accountnumber=ISNULL(@account,c.accountnumber)--account
	 AND c.FirstName=ISNULL(@FN, c.FirstName)
	 AND c.LastName=ISNULL(@ln, c.LastName)--customer
	 AND tr.time=ISNULL(@dt,tr.time)--date
	 AND tr.Comment LIKE '%'+ISNULL(@attendee,tr.Comment)+'%'
	 --program
	 )
	 order by tr.id, tr.time;
RETURN
END

Open in new window

0
ZberteocCommented:
All you need is this:
USE [KingsBayY]
GO
/****** Object:  UserDefinedFunction [dbo].[test_fn_transaction_search]    Script Date: 03/18/2015 00:12:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[test_fn_transaction_search](@receiptnum varchar(4001) ='',--00040401010000021
												@account varchar(4001) ='',
												@FN varchar(4001) ='',
												@ln varchar(4001) ='',--DateTime = '',
												@dt varchar(4001) ='',
												@program varchar(4001) ='',--)
												@attendee varchar(4001) ='')
												--stdt
												--enddt
						
RETURNS @resulttable TABLE
   (trid  int,
    trdate   varchar(4001), --COLLATE database_default
    tramount       money,
    trinvoice  varchar(4001),
    trcashierid varchar(4001),
    trvoided varchar(4001),
    trrecallid varchar(4001),
    trrecalltype varchar(4001),
    trattendee varchar(4001),
    trperiod varchar(4001),
    stopbilling varchar(4001),
    caccount varchar(4001),
    firstname varchar(4001),
    lastname varchar(4001),
    trprogram varchar(4001),
    itemlookupcode varchar(4001),
    paydate varchar(4001)
    )
AS


BEGIN
IF (@receiptnum='' and @account='' and @FN=''and @ln='' and @dt='' and @program='' and @attendee='')
	INSERT INTO @resulttable
select 
	tr.ID AS [Transaction ID], tr.Time AS [trDt], tr.Total AS [amount], 
	tr.ReceiptNumber AS [invoice], tr.CashierID AS [cashid], 
	tr.Voided AS [voided], tr.RecallID AS [recallid], tr.RecallType AS [recalltype], 
	tr.Comment AS [attendee], tr.ReferenceNumber AS [trperiod], tr.StopBilling  AS [stopbilling],
	--fields from customer
	c.AccountNumber AS [accountnum], c.FirstName AS [firstname], c.LastName AS [lastname],
	--fields from item
	it.Description AS [programname], it.ItemLookupCode AS [trItemLookupCode],
	--fields from tenderentry
	tent.PaymentDate AS [paydate]
from 
	[Transaction] tr
	inner join TransactionEntry trent
		on	trent.TransactionID = tr.ID
	inner join Item it
		on trent.ItemID = it.ID
	inner join Customer c
		on c.ID=tr.CustomerID
	inner join TenderEntry tent
		on tent.TransactionID = tr.ID
where
	tr.RecallID =0 --parent transactions and independant sales
	--and tr.RecallType=0 --
	and tr.ReceiptNumber= ISNULL(@receiptnum,tr.ReceiptNumber)--invoice
	and c.accountnumber= ISNULL(@account,c.accountnumber)--account
	and c.FirstName= ISNULL(@FN,c.FirstName)
	and c.LastName= ISNULL(@ln,c.LastName)--customer
	and tr.time= ISNULL(@dt,tr.time)--date
	and tr.Comment=ISNULL(@attendee,tr.Comment)
order by tr.id, tr.time;
RETURN
END

Open in new window


Whatever parameter(s) will be passed will influence the result by filtering accordingly. if a parameter is null will not have any effect. Basically those IF statements are redundant and you don't need them.

The union doesn't make any sense. If a column value is null how do you know if you need to return it or not? Also using ISNULL() around the columns will prevent the SQL engine to use any index so your query will be slow. I also modified to use the new SQL syntax with INNER JOIN rather than the old and deprecated style with comas.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
solarisinfosys, do you still help with this question?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.