Solved

Using COALESCE in stored procedure for searching DB records.

Posted on 2010-09-02
4
584 Views
Last Modified: 2012-05-10
I have an application that creates a query string that I use to search a SQL SERVER 2008 EXPRESS database.  Most of the code is to create the WHERE clause of the query.

With much help from some of the experts here I have created a stored procedure that takes the string of the WHERE clause as an input and returns the desired results.  While doing this I stumbled across the function COALESCE.

From my understanding I should be able to produce a SP (using COALESCE or ISNULL) that will create the entire search query for me with relatively little code.  Removing the need for most of the code that I am currently using in my app.

Below is the SQL code using COALESCE that I am trying to get to work.  My test results for this code along with the errors are attached (testresults.xls).

Basically I am trying to create a stored procedure that will take the text entered in text and or listboxes and return the records based on that search.  Much like the advance search of EE.

Thank you for any and all help.


USE [Data]

GO

/****** Object:  StoredProcedure [dbo].[usp_SearchResults2]    Script Date: 09/01/2010 22:40:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



-- =============================================

-- Author:		

-- Create date: 8/29/2010

-- Description:	

-- =============================================

ALTER PROCEDURE [dbo].[usp_SearchResults2] 

	-- Add the parameters for the stored procedure here

	@Item nvarchar(19),

	@ProdLine nvarchar(50),

	@Document_Type nvarchar(50)

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    --Insert statements for procedure here

    IF (RIGHT(@Item,2)='xx') 

		BEGIN

			SET @Item = SUBSTRING(@Item,0,6)

		END



	SELECT Item_Number,

		   ProdLine,

		   Document_Type

	FROM   tblItem

	WHERE		   

		   Item_Number LIKE '%' + COALESCE(@Item,Item_Number) + '%'  AND

		   ProdLine LIKE '%' + COALESCE(@ProdLine,ProdLine) + '%' AND

		   Document_Type = COALESCE(@Document_Type,Document_Type)

END

Open in new window

testresults.xls
0
Comment
Question by:NevSoFly
4 Comments
 
LVL 12

Assisted Solution

by:patrikt
patrikt earned 100 total points
ID: 33584440
I dont't know what your expected results are but I can describe COALESCE function.

It will return first not null argument. But you are not allowing @Item to be optional so your procedure must be called wth this parameter. If you want @Item or other parameter to be optional add default value:
PROCEDURE [dbo].[usp_SearchResults2]
      -- Add the parameters for the stored procedure here
      @Item nvarchar(19)=NULL,
      @ProdLine nvarchar(50),
      @Document_Type nvarchar(50)

In this case if you will not pass @Item it will be assigned NULL and
COALESCE(@Item,Item_Number) will be evaluated as Item_Number.

0
 
LVL 22

Accepted Solution

by:
pivar earned 400 total points
ID: 33584457
Hi,

Is it this you're looking for?

      SELECT Item_Number,
               ProdLine,
               Document_Type
      FROM   tblItem
      WHERE (@Item IS NULL OR Item_Number LIKE '%' + @Item + '%')  
             AND (@ProdLine IS NULL OR ProdLine LIKE '%' + @ProdLine + '%')
             AND (@Document_Type IS NULL OR Document_Type = Document_Type)

/peter
0
 
LVL 8

Expert Comment

by:rushShah
ID: 33584958
hi, try with brackets,

USE [Data]

GO

/****** Object:  StoredProcedure [dbo].[usp_SearchResults2]    Script Date: 09/01/2010 22:40:27 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO



-- =============================================

-- Author:		

-- Create date: 8/29/2010

-- Description:	

-- =============================================

ALTER PROCEDURE [dbo].[usp_SearchResults2] 

	-- Add the parameters for the stored procedure here

	@Item nvarchar(19),

	@ProdLine nvarchar(50),

	@Document_Type nvarchar(50)

AS

BEGIN

	-- SET NOCOUNT ON added to prevent extra result sets from

	-- interfering with SELECT statements.

	SET NOCOUNT ON;



    --Insert statements for procedure here

    IF (RIGHT(@Item,2)='xx') 

		BEGIN

			SET @Item = SUBSTRING(@Item,0,6)

		END



	SELECT Item_Number,

		   ProdLine,

		   Document_Type

	FROM   tblItem

	WHERE		   

		   (Item_Number LIKE '%' + COALESCE(@Item,Item_Number) + '%')  AND

		   (ProdLine LIKE '%' + COALESCE(@ProdLine,ProdLine) + '%') AND

		   (Document_Type = COALESCE(@Document_Type,Document_Type))

END

Open in new window

0
 

Author Closing Comment

by:NevSoFly
ID: 33587933
pivar your code did the trick and along with patrikt's suggestion of setting the default refferance to NULL everything turned out fine and saved me several dozen lines of VB code.

thanks
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now