Need help with sqlserver search stored procedure.

28/08/2008 - 23:49 por aboutjav.com | Informe spam
HI,

I am writing a stored procedure for my search box control. I found
this sample code which sets the @WORD1 to 'TBB-12' and searches 1
table for the information. I set the value to TBB-12 and specified to
look in the dbo.product_id. It did find the product id = TBB-12 on the
first result row, however, it return a bunch other rows, which none of
the column fields contains the value 'TBB-12'. In theory, a search
should only return 1 row, correct? Why am I getting a bunch extra
useless rows? What is wrong with the sql query?



Thanks,



John



3 * dbo.WordCount('TBB-12', (actress1.fullname + ' '
+actress2.fullname + actress3.fullname + ' ' + actress4.fullname + '
'+ actress5.fullname + ' '+ actress6.fullname + ' '+ actress7.fullname
+ ' '+ actress8.fullname + ' '+ actress9.fullname + ' '+
actress10.fullname)) + dbo.WordCount('TBB-12', dbo.dvd.product_id)







SELECT dbo.dvd.dvdid,
COALESCE(actress1.fullname+',',actress2.fullname+',',actress3.fullname
+',',actress4.fullname+',',actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress2.fullname+',',actress3.fullname
+',',actress4.fullname+',',actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress3.fullname+',',actress4.fullname
+',',actress5.fullname+',',actress6.fullname+',',actress7.fullname
+',',actress8.fullname+',',actress9.fullname+',',actress10.fullname
+',','')
+ COALESCE(actress4.fullname+',',actress5.fullname
+',',actress6.fullname+',',actress7.fullname+',',actress8.fullname
+',',actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress6.fullname+',',actress7.fullname
+',',actress8.fullname+',',actress9.fullname+',',actress10.fullname
+',','')
+ COALESCE(actress7.fullname+',',actress8.fullname
+',',actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress10.fullname+',',''),
dbo.dvd.title,
dbo.dvd.product_id,
dbo.dvd.num_discs,
dbo.dvd.description,
CAST(ROUND(dbo.price.price,2) AS DECIMAL(4,2)),
dbo.dvd.cover,
3 * dbo.WordCount('TBB-12', (actress1.fullname + ' '
+actress2.fullname + actress3.fullname + ' ' + actress4.fullname + '
'+ actress5.fullname + ' '+ actress6.fullname + ' '+ actress7.fullname
+ ' '+ actress8.fullname + ' '+ actress9.fullname + ' '+
actress10.fullname)) + dbo.WordCount('TBB-12', dbo.dvd.product_id)
AS rank
from dbo.dvd
LEFT JOIN dbo.actress actress1 ON actress1.actressid dbo.dvd.actressid1
LEFT JOIN dbo.actress actress2 ON actress2.actressid dbo.dvd.actressid2
LEFT JOIN dbo.actress actress3 ON actress3.actressid dbo.dvd.actressid3
LEFT JOIN dbo.actress actress4 ON actress4.actressid dbo.dvd.actressid4
LEFT JOIN dbo.actress actress5 ON actress5.actressid dbo.dvd.actressid5
LEFT JOIN dbo.actress actress6 ON actress6.actressid dbo.dvd.actressid6
LEFT JOIN dbo.actress actress7 ON actress7.actressid dbo.dvd.actressid7
LEFT JOIN dbo.actress actress8 ON actress8.actressid dbo.dvd.actressid8
LEFT JOIN dbo.actress actress9 ON actress9.actressid dbo.dvd.actressid9
LEFT JOIN dbo.actress actress10 ON actress10.actressid dbo.dvd.actressid10
inner join dbo.actress
on dbo.dvd.actressid1 = dbo.actress.actressid
inner join dbo.price
on dbo.price.quantity = dbo.dvd.num_discs and dbo.price.producttype 'dvd'
order by rank desc
 

Leer las respuestas

#1 Gustavo Larriera (MVP)
29/08/2008 - 16:06 | Informe spam
Hi,

you should move your post to the English discussion group or post it here in
Spanish.

Best regards,
~gux

Gustavo Larriera, Microsoft MVP
http://www.linkedin.com/in/gustavolarriera
Este mensaje se proporciona tal como es, sin garantías de ninguna clase.



"" wrote:

HI,

I am writing a stored procedure for my search box control. I found
this sample code which sets the @WORD1 to 'TBB-12' and searches 1
table for the information. I set the value to TBB-12 and specified to
look in the dbo.product_id. It did find the product id = TBB-12 on the
first result row, however, it return a bunch other rows, which none of
the column fields contains the value 'TBB-12'. In theory, a search
should only return 1 row, correct? Why am I getting a bunch extra
useless rows? What is wrong with the sql query?



Thanks,



John



3 * dbo.WordCount('TBB-12', (actress1.fullname + ' '
+actress2.fullname + actress3.fullname + ' ' + actress4.fullname + '
'+ actress5.fullname + ' '+ actress6.fullname + ' '+ actress7.fullname
+ ' '+ actress8.fullname + ' '+ actress9.fullname + ' '+
actress10.fullname)) + dbo.WordCount('TBB-12', dbo.dvd.product_id)







SELECT dbo.dvd.dvdid,
COALESCE(actress1.fullname+',',actress2.fullname+',',actress3.fullname
+',',actress4.fullname+',',actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress2.fullname+',',actress3.fullname
+',',actress4.fullname+',',actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress3.fullname+',',actress4.fullname
+',',actress5.fullname+',',actress6.fullname+',',actress7.fullname
+',',actress8.fullname+',',actress9.fullname+',',actress10.fullname
+',','')
+ COALESCE(actress4.fullname+',',actress5.fullname
+',',actress6.fullname+',',actress7.fullname+',',actress8.fullname
+',',actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress5.fullname+',',actress6.fullname
+',',actress7.fullname+',',actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress6.fullname+',',actress7.fullname
+',',actress8.fullname+',',actress9.fullname+',',actress10.fullname
+',','')
+ COALESCE(actress7.fullname+',',actress8.fullname
+',',actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress8.fullname+',',actress9.fullname
+',',actress10.fullname+',','')
+ COALESCE(actress9.fullname+',',actress10.fullname+',','')
+ COALESCE(actress10.fullname+',',''),
dbo.dvd.title,
dbo.dvd.product_id,
dbo.dvd.num_discs,
dbo.dvd.description,
CAST(ROUND(dbo.price.price,2) AS DECIMAL(4,2)),
dbo.dvd.cover,
3 * dbo.WordCount('TBB-12', (actress1.fullname + ' '
+actress2.fullname + actress3.fullname + ' ' + actress4.fullname + '
'+ actress5.fullname + ' '+ actress6.fullname + ' '+ actress7.fullname
+ ' '+ actress8.fullname + ' '+ actress9.fullname + ' '+
actress10.fullname)) + dbo.WordCount('TBB-12', dbo.dvd.product_id)
AS rank
from dbo.dvd
LEFT JOIN dbo.actress actress1 ON actress1.actressid > dbo.dvd.actressid1
LEFT JOIN dbo.actress actress2 ON actress2.actressid > dbo.dvd.actressid2
LEFT JOIN dbo.actress actress3 ON actress3.actressid > dbo.dvd.actressid3
LEFT JOIN dbo.actress actress4 ON actress4.actressid > dbo.dvd.actressid4
LEFT JOIN dbo.actress actress5 ON actress5.actressid > dbo.dvd.actressid5
LEFT JOIN dbo.actress actress6 ON actress6.actressid > dbo.dvd.actressid6
LEFT JOIN dbo.actress actress7 ON actress7.actressid > dbo.dvd.actressid7
LEFT JOIN dbo.actress actress8 ON actress8.actressid > dbo.dvd.actressid8
LEFT JOIN dbo.actress actress9 ON actress9.actressid > dbo.dvd.actressid9
LEFT JOIN dbo.actress actress10 ON actress10.actressid > dbo.dvd.actressid10
inner join dbo.actress
on dbo.dvd.actressid1 = dbo.actress.actressid
inner join dbo.price
on dbo.price.quantity = dbo.dvd.num_discs and dbo.price.producttype > 'dvd'
order by rank desc

Preguntas similares