asked on

Using COALESCE in stored procedure for searching DB records.

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

SOLUTION

patrikt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rushShah

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

NevSoFly

ASKER

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