August 29, 2008 at 6:39 am
As a fairly new SQL developer, I have often struggled with creating dynamic queries. This article is useful because it collects several techniques in one place. Some of these I knew about and others I did not. I find the use of ISNULL is a D'oh moment for me....
However, I was required to create 3 field search functionality using partial matching. Here is what I developed using the example from the article:
CREATE PROCEDURE ProductSearch2
(
@ProductNumber VARCHAR(20),
@Color VARCHAR(10),
@ReOrderPoint INT
)
AS
SET NOCOUNT ON
SELECT *
FROM Production.Product
WHERE ProductNumber = COALESCE('%' + @ProductNumber + '%', ProductNumber)
AND Color = COALESCE('%' + @Color + '%', Color)
AND ReorderPoint = COALESCE('%' + @ReorderPoint + '%', ReorderPoint)
I could probably take advantage of the ISNULL function but that would create a lot of rework. Is there a reason I should not use COALESCE?
August 29, 2008 at 6:44 am
Please remove, posed in the wrong area...sorry
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply