1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
| CREATE procedure [dbo].[sprech_all]
@recherche varchar(256),
@nbr int=20 /* nombre de resultats remonté par CONTAINSTABLE
AS
BEGIN
DECLARE @predicatFTS VARCHAR(512)
DECLARE @nb int
SET @nb=charindex('+',@recherche)
if @nb >0
BEGIN
DECLARE @st1 varchar(64)
DECLARE @st2 varchar(64)
SET @st1=substring(@recherche,1,@nb-1)
SET @st2=substring(@recherche,@nb+1,len(@recherche)-@nb)
SELECT @predicatFTS = 'FORMSOF (INFLECTIONAL,' + QUOTENAME(@st1,'"') + ') AND FORMSOF (INFLECTIONAL,' + QUOTENAME(@st2,'"') + ')'
END
else
BEGIN
SELECT @predicatFTS = 'FORMSOF (INFLECTIONAL,' + QUOTENAME(@recherche,'"') + ')'
END
Select top 100 percent * from vste_recherche_complet as VC
where
(idste in
(
SELECT ste.idste
FROM STEPRODUITS AS STE INNER JOIN
CONTAINSTABLE (STEPRODUITS, *,
@predicatFTS , @nbr
) AS KEY_TBL
ON STE.IDSTE = KEY_TBL.[KEY]
)
OR
idste in
(
SELECT ste.idste
FROM STEactiv AS STE INNER JOIN
CONTAINSTABLE (STEactiv, *,
@predicatFTS , @nbr
) AS KEY_TBL
ON STE.IDSTE = KEY_TBL.[KEY]
))
order by actif DESC , premium desc
END |
Partager