Using a Stored Procedure with a contains statement and wildcards

Question

<br/>(<br/>@NounSearch nvarchar(60) = '""',

@DescriptionSearch nvarchar(60)= '""',

	

)

AS

	SET NOCOUNT ON;

	

SELECT     TOP (1000) p.ps_pik, p.item_pik, p.mfr_ucc_num, p.item_num, p.i2_cat_num, p.mfr_cat_num, p.product_name, p.fulltech_description, p.mfr_description, 

                      p.invoice_description, p.comm_pik, p.ps_uom, p.ps_uom_qty, p.list, p.col3, p.resale, p.mfr_pik, p.leaf_class, p.pfms_description, p.xref_group, p.[2K_DESC], 

                      p.brand_name, p.upc, m.mfr_fullname, i_image.item_pik AS Expr1,(ib_image.image_basepath  + '/' + i_image.image_subdir + '/' + i_image.image_filename )AS img_url,

		      i_thumb.item_pik as Expr2, (ib_thumb.image_basepath  + '/' + i_thumb.image_subdir + '/' + i_thumb.image_filename )AS thumb_url,

                      i_pdf.item_pik AS Expr3,(ib_pdf.image_basepath  + '/' + i_pdf.image_subdir + '/' + i_pdf.image_filename )AS pdf_url

FROM         pricesvc AS p LEFT OUTER JOIN

                      mfr AS m ON m.mfr_pik = p.mfr_pik 

                      LEFT OUTER JOIN

                      image AS i_image ON i_image.item_pik = p.item_pik AND i_image.image_basepath_pik =1020

                      LEFT OUTER JOIN

                      image_basepath AS ib_image ON ib_image.image_basepath_pik = i_image.image_basepath_pik

                      LEFT OUTER JOIN

                      image AS i_thumb ON i_thumb.item_pik = p.item_pik AND i_thumb.image_basepath_pik =1040

                      LEFT OUTER JOIN

                      image_basepath AS ib_thumb ON ib_thumb.image_basepath_pik = i_thumb.image_basepath_pik

                      LEFT OUTER JOIN

                      image AS i_pdf ON i_pdf.item_pik = p.item_pik AND i_pdf.image_basepath_pik =1021

                      LEFT OUTER JOIN

                      image_basepath AS ib_pdf ON ib_pdf.image_basepath_pik = i_pdf.image_basepath_pik

WHERE   (CONTAINS (p.product_name,  @NounSearch) AND CONTAINS (p.*, @DescriptionSearch))

an example of what I am passing into the stored procedure is
@NounSearch = "Valve"
@DescriptionSearch = "1/2" AND "Inch"

That works PERFECT!  Now for the problem.  If I want to pass a null value through one of these parameters, I don't get back any results.  What is the best way to pass in a null value as a wildcard? or the other way around what is the way of passing a wildcard in instead of a null value.
IE.

@NounSearch = "NULL"
@DescriptionSearch = "1/2" AND "Inch"

OR

@NounSearch = "**%%"  <-- wildcards haha
@DescriptionSearch = "1/2" AND "Inch"

---

Harley

Answer 1

I'd try something like this:

create proc test (@NounSearch nvarchar(60) = null,@DescriptionSearch nvarchar(60) = null)
as
SET NOCOUNT ON;
if @NounSearch is null and @DescriptionSearch is null
begin
SELECT TOP (1000) p.ps_pik, p.item_pik, p.mfr_ucc_num, p.item_num, p.i2_cat_num, 
p.mfr_cat_num, p.product_name, p.fulltech_description, p.mfr_description, 
p.invoice_description, p.comm_pik, p.ps_uom, p.ps_uom_qty, p.list, p.col3, p.resale, 
p.mfr_pik, p.leaf_class, p.pfms_description, p.xref_group, p.[2K_DESC], 
p.brand_name, p.upc, m.mfr_fullname, i_image.item_pik AS Expr1,
(ib_image.image_basepath  + '/' + i_image.image_subdir + '/' + i_image.image_filename )AS img_url,
i_thumb.item_pik as Expr2, 
(ib_thumb.image_basepath  + '/' + i_thumb.image_subdir + '/' + i_thumb.image_filename )AS thumb_url,
i_pdf.item_pik AS Expr3,
(ib_pdf.image_basepath  + '/' + i_pdf.image_subdir + '/' + i_pdf.image_filename )AS pdf_url
FROM pricesvc AS p 
LEFT OUTER JOIN mfr AS m ON m.mfr_pik = p.mfr_pik 
LEFT OUTER JOIN image AS i_image ON i_image.item_pik = p.item_pik AND 
i_image.image_basepath_pik =1020
LEFT OUTER JOIN image_basepath AS ib_image ON ib_image.image_basepath_pik = i_image.image_basepath_pik
LEFT OUTER JOIN image AS i_thumb ON i_thumb.item_pik = p.item_pik AND 
i_thumb.image_basepath_pik =1040
LEFT OUTER JOIN image_basepath AS ib_thumb ON ib_thumb.image_basepath_pik = i_thumb.image_basepath_pik
LEFT OUTER JOIN image AS i_pdf ON i_pdf.item_pik = p.item_pik AND 
i_pdf.image_basepath_pik =1021
LEFT OUTER JOIN image_basepath AS ib_pdf ON ib_pdf.image_basepath_pik = i_pdf.image_basepath_pik
return
end
if @NounSearch is null and @DescriptionSearch is not null
begin
SELECT TOP (1000) p.ps_pik, p.item_pik, p.mfr_ucc_num, p.item_num, p.i2_cat_num, 
p.mfr_cat_num, p.product_name, p.fulltech_description, p.mfr_description, 
p.invoice_description, p.comm_pik, p.ps_uom, p.ps_uom_qty, p.list, p.col3, p.resale, 
p.mfr_pik, p.leaf_class, p.pfms_description, p.xref_group, p.[2K_DESC], 
p.brand_name, p.upc, m.mfr_fullname, i_image.item_pik AS Expr1,
(ib_image.image_basepath  + '/' + i_image.image_subdir + '/' + i_image.image_filename )AS img_url,
i_thumb.item_pik as Expr2, 
(ib_thumb.image_basepath  + '/' + i_thumb.image_subdir + '/' + i_thumb.image_filename )AS thumb_url,
i_pdf.item_pik AS Expr3,
(ib_pdf.image_basepath  + '/' + i_pdf.image_subdir + '/' + i_pdf.image_filename )AS pdf_url
FROM pricesvc AS p 
LEFT OUTER JOIN mfr AS m ON m.mfr_pik = p.mfr_pik 
LEFT OUTER JOIN image AS i_image ON i_image.item_pik = p.item_pik AND 
i_image.image_basepath_pik =1020
LEFT OUTER JOIN image_basepath AS ib_image ON ib_image.image_basepath_pik = i_image.image_basepath_pik
LEFT OUTER JOIN image AS i_thumb ON i_thumb.item_pik = p.item_pik AND 
i_thumb.image_basepath_pik =1040
LEFT OUTER JOIN image_basepath AS ib_thumb ON ib_thumb.image_basepath_pik = i_thumb.image_basepath_pik
LEFT OUTER JOIN image AS i_pdf ON i_pdf.item_pik = p.item_pik AND 
i_pdf.image_basepath_pik =1021
LEFT OUTER JOIN image_basepath AS ib_pdf ON ib_pdf.image_basepath_pik = i_pdf.image_basepath_pik
WHERE CONTAINS (p.*, @DescriptionSearch)
return
end
if @NounSearch is not null and @DescriptionSearch is null
begin
SELECT TOP (1000) p.ps_pik, p.item_pik, p.mfr_ucc_num, p.item_num, p.i2_cat_num, 
p.mfr_cat_num, p.product_name, p.fulltech_description, p.mfr_description, 
p.invoice_description, p.comm_pik, p.ps_uom, p.ps_uom_qty, p.list, p.col3, p.resale, 
p.mfr_pik, p.leaf_class, p.pfms_description, p.xref_group, p.[2K_DESC], 
p.brand_name, p.upc, m.mfr_fullname, i_image.item_pik AS Expr1,
(ib_image.image_basepath  + '/' + i_image.image_subdir + '/' + i_image.image_filename )AS img_url,
i_thumb.item_pik as Expr2, 
(ib_thumb.image_basepath  + '/' + i_thumb.image_subdir + '/' + i_thumb.image_filename )AS thumb_url,
i_pdf.item_pik AS Expr3,
(ib_pdf.image_basepath  + '/' + i_pdf.image_subdir + '/' + i_pdf.image_filename )AS pdf_url
FROM pricesvc AS p 
LEFT OUTER JOIN mfr AS m ON m.mfr_pik = p.mfr_pik 
LEFT OUTER JOIN image AS i_image ON i_image.item_pik = p.item_pik AND 
i_image.image_basepath_pik =1020
LEFT OUTER JOIN image_basepath AS ib_image ON ib_image.image_basepath_pik = i_image.image_basepath_pik
LEFT OUTER JOIN image AS i_thumb ON i_thumb.item_pik = p.item_pik AND 
i_thumb.image_basepath_pik =1040
LEFT OUTER JOIN image_basepath AS ib_thumb ON ib_thumb.image_basepath_pik = i_thumb.image_basepath_pik
LEFT OUTER JOIN image AS i_pdf ON i_pdf.item_pik = p.item_pik AND 
i_pdf.image_basepath_pik =1021
LEFT OUTER JOIN image_basepath AS ib_pdf ON ib_pdf.image_basepath_pik = i_pdf.image_basepath_pik
WHERE CONTAINS (p.product_name,  @NounSearch) 
return
end
if @NounSearch is not null and @DescriptionSearch is not null
begin
SELECT TOP (1000) p.ps_pik, p.item_pik, p.mfr_ucc_num, p.item_num, p.i2_cat_num, 
p.mfr_cat_num, p.product_name, p.fulltech_description, p.mfr_description, 
p.invoice_description, p.comm_pik, p.ps_uom, p.ps_uom_qty, p.list, p.col3, p.resale, 
p.mfr_pik, p.leaf_class, p.pfms_description, p.xref_group, p.[2K_DESC], 
p.brand_name, p.upc, m.mfr_fullname, i_image.item_pik AS Expr1,
(ib_image.image_basepath  + '/' + i_image.image_subdir + '/' + i_image.image_filename )AS img_url,
i_thumb.item_pik as Expr2, 
(ib_thumb.image_basepath  + '/' + i_thumb.image_subdir + '/' + i_thumb.image_filename )AS thumb_url,
i_pdf.item_pik AS Expr3,
(ib_pdf.image_basepath  + '/' + i_pdf.image_subdir + '/' + i_pdf.image_filename )AS pdf_url
FROM pricesvc AS p 
LEFT OUTER JOIN mfr AS m ON m.mfr_pik = p.mfr_pik 
LEFT OUTER JOIN image AS i_image ON i_image.item_pik = p.item_pik AND 
i_image.image_basepath_pik =1020
LEFT OUTER JOIN image_basepath AS ib_image ON ib_image.image_basepath_pik = i_image.image_basepath_pik
LEFT OUTER JOIN image AS i_thumb ON i_thumb.item_pik = p.item_pik AND 
i_thumb.image_basepath_pik =1040
LEFT OUTER JOIN image_basepath AS ib_thumb ON ib_thumb.image_basepath_pik = i_thumb.image_basepath_pik
LEFT OUTER JOIN image AS i_pdf ON i_pdf.item_pik = p.item_pik AND 
i_pdf.image_basepath_pik =1021
LEFT OUTER JOIN image_basepath AS ib_pdf ON ib_pdf.image_basepath_pik = i_pdf.image_basepath_pik
WHERE  (CONTAINS (p.product_name,  @NounSearch) AND CONTAINS (p.*, @DescriptionSearch))
return
end

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Hi, Hilary Thanks for your reply. 

We are attacking this from ASP.net using the approach

 page -> Business Logic Layer -> Data Access Layer and the DAL is using Stored Procedures, not paramatized queries. 

It would not let us pass NULL through the DAL to the stored procedure using this approach.  We took the route of creating multiple stored procedures for each kind of query we would need, a total of about 6 (so far) and used the BLL to define which stored procedure to use.    Its working great!  I just wish you could pass in something similar to a wildcard for the like statement :) would make this a breeeeeze! 

Thanks again :P we are moving forward on this now haha!

---

Harley

Answer 3

If that is the case then craft your is null statments from this:

if @NounSearch is null and @DescriptionSearch is null

to this

if @NounSearch ='*' and @DescriptionSearch ='*'

replacing * with your wild card character of choice.

---

looking for a book on SQL Server replication? http://www.nwsu.com/0974973602.html looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 4

actually we were on that route already!  :P "%" default from our like statements. 

Thanks for your help :P we have everything working right now without the route you were talking about, but we are going to explore that route too to narrow down a couple of these stored procedures.

:P

---

Harley