Building Search String ?

The procedure below is for a 3 field wildcard search window.

It works fine if you enter a value such as cheese in @product_name.
If you enter cheese in @product_name1 or @product_name2 it returns nothing.

The string builds OK with values inserted where they should be, obviously there is a problem in the looping back of variable.

I've been looking at this so long I'm sure that the obvious solution is staring me in the face, but I just can't see it.

Any help would be appreciated.


Alter PROCEDURE usp_RobSearchTest
(
@product_name nVarChar(100),
@product_name1 nVarChar(100),
@product_name2 nVarChar(100),
@country_id nchar (3),
@language_id nchar (2)
)
AS




DECLARE @Variable nVarChar (100)

DECLARE @SQLString NVARCHAR(1000)

DECLARE @ParmDefinition NVARCHAR(1000)





/* Build the SQL string once. */

SET @SQLString =

N'SELECT product_name,unit_price,item_id,refund_price
FROM PRODUCT_LISTING
WHERE product_name LIKE ''%' + @Product_name + '%''
ORDER BY product_name'


/* Specify the parameter format once. */

SET @ParmDefinition = N'@product_name nVarChar (100)'

/* Execute the string with the first parameter value. */

SET @Variable = @product_name

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@product_name = @Variable


/* IT WORKS UP TO THIS POINT */
/* Execute the same string with the second parameter value. */

SET @Variable = @product_name1

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@product_name1 = @Variable

/* Execute the same string with the third parameter value. */

SET @Variable = @product_name2

EXECUTE sp_executesql @SQLString, @ParmDefinition,

@product_name2 = @Variable