Optimise ma requette SQL
cs_samyt26
Messages postés
3
Date d'inscription
dimanche 22 juin 2003
Statut
Membre
Dernière intervention
12 janvier 2006
-
12 janv. 2006 à 12:26
cs_samyt26 Messages postés 3 Date d'inscription dimanche 22 juin 2003 Statut Membre Dernière intervention 12 janvier 2006 - 12 janv. 2006 à 13:06
cs_samyt26 Messages postés 3 Date d'inscription dimanche 22 juin 2003 Statut Membre Dernière intervention 12 janvier 2006 - 12 janv. 2006 à 13:06
A voir également:
- Optimise ma requette SQL
- Requete paramétrée sql - Conseils pratiques -C# / .NET
- Decimal sql - Forum Visual Basic
- Requete sql - Forum SQL
- Commit sql ✓ - Forum SQL
- Requete sql between ✓ - Forum SQL
2 réponses
cs_samyt26
Messages postés
3
Date d'inscription
dimanche 22 juin 2003
Statut
Membre
Dernière intervention
12 janvier 2006
12 janv. 2006 à 13:05
12 janv. 2006 à 13:05
CREATE PROCEDURE searchInsideAllExact
@prefix nvarchar(50),
@search nvarchar(50)
AS
declare
@prefix1 nvarchar(50),
@prefix2 nvarchar(50),
@search1 nvarchar(50),
@search2 nvarchar(50),
@prefixOR nvarchar(50),
@searchOR nvarchar(50),
@prefix3 nvarchar(50)
set @prefix3 = '%[a-z]%'
set @prefix1 = @prefix+'%'
set @prefix2 ='%'+@prefix
set @search1 = @search+'%'
set @search2 = '%'+@search+'%'
set @prefixOR = '%'+@prefix+'%'
set @searchOR = '%'+@search+'%'
if @search = ''
SELECT 1 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart '' AND sPrefixPart @prefix
UNION
SELECT 2 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart '' AND sPrefixPart @prefix
UNION
SELECT 3 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart '' AND sPrefixPart @prefix
UNION
SELECT 4 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sPrefixPart = @prefix
UNION
SELECT 5 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sPrefixPart = @prefix
UNION
SELECT 6 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sPrefixPart = @prefix
ORDER BY ttype,sPart, dLastUpdate desc
else
SELECT 1 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart @search AND sPrefixPart @prefix
UNION
SELECT 2 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart @search AND sPrefixPart @prefix
UNION
SELECT 3 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart @search AND sPrefixPart @prefix
UNION
SELECT 4 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart like @search1 AND sPrefixPart @prefix and not (sSearchPart @search AND sPrefixPart = @prefix)
UNION
SELECT 5 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart like @search1 AND sPrefixPart @prefix and not (sSearchPart @search AND sPrefixPart = @prefix)
UNION
SELECT 6 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart like @search1 AND sPrefixPart @prefix and not (sSearchPart @search AND sPrefixPart = @prefix)
UNION
SELECT 7 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart like @search1 AND sPrefixPart like @prefix3 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix))
UNION
SELECT 8 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart like @search1 AND sPrefixPart like @prefix3 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix))
UNION
SELECT 9 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart like @search1 AND sPrefixPart like @prefix3 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix))
UNION
SELECT 10 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart like @search1 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix) and ( sSearchPart not like @search1 or sPrefixPart not like @prefix3))
union
SELECT 11 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart like @search1 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix) and ( sSearchPart not like @search1 or sPrefixPart not like @prefix3))
union
SELECT 12 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart like @search1 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix) and ( sSearchPart not like @search1 or sPrefixPart not like @prefix3))
ORDER BY ttype,sPart, dLastUpdate desc
GO
@prefix nvarchar(50),
@search nvarchar(50)
AS
declare
@prefix1 nvarchar(50),
@prefix2 nvarchar(50),
@search1 nvarchar(50),
@search2 nvarchar(50),
@prefixOR nvarchar(50),
@searchOR nvarchar(50),
@prefix3 nvarchar(50)
set @prefix3 = '%[a-z]%'
set @prefix1 = @prefix+'%'
set @prefix2 ='%'+@prefix
set @search1 = @search+'%'
set @search2 = '%'+@search+'%'
set @prefixOR = '%'+@prefix+'%'
set @searchOR = '%'+@search+'%'
if @search = ''
SELECT 1 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart '' AND sPrefixPart @prefix
UNION
SELECT 2 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart '' AND sPrefixPart @prefix
UNION
SELECT 3 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart '' AND sPrefixPart @prefix
UNION
SELECT 4 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sPrefixPart = @prefix
UNION
SELECT 5 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sPrefixPart = @prefix
UNION
SELECT 6 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sPrefixPart = @prefix
ORDER BY ttype,sPart, dLastUpdate desc
else
SELECT 1 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart @search AND sPrefixPart @prefix
UNION
SELECT 2 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart @search AND sPrefixPart @prefix
UNION
SELECT 3 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart @search AND sPrefixPart @prefix
UNION
SELECT 4 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart like @search1 AND sPrefixPart @prefix and not (sSearchPart @search AND sPrefixPart = @prefix)
UNION
SELECT 5 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart like @search1 AND sPrefixPart @prefix and not (sSearchPart @search AND sPrefixPart = @prefix)
UNION
SELECT 6 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart like @search1 AND sPrefixPart @prefix and not (sSearchPart @search AND sPrefixPart = @prefix)
UNION
SELECT 7 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart like @search1 AND sPrefixPart like @prefix3 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix))
UNION
SELECT 8 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart like @search1 AND sPrefixPart like @prefix3 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix))
UNION
SELECT 9 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart like @search1 AND sPrefixPart like @prefix3 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix))
UNION
SELECT 10 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_stock
WHERE sSearchPart like @search1 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix) and ( sSearchPart not like @search1 or sPrefixPart not like @prefix3))
union
SELECT 11 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_available
WHERE sSearchPart like @search1 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix) and ( sSearchPart not like @search1 or sPrefixPart not like @prefix3))
union
SELECT 12 AS ttype, nIndex, nID_client, sPart, sPrefixPart,sSearchPart, sDescription, nQuantity, sMfg, nDc, dLastUpdate, nAuction
FROM tbl_offer
WHERE sSearchPart like @search1 and ((sSearchPart not like @search1 or sPrefixPart <> @prefix) and (sSearchPart <> @search or sPrefixPart <> @prefix) and ( sSearchPart not like @search1 or sPrefixPart not like @prefix3))
ORDER BY ttype,sPart, dLastUpdate desc
GO
cs_samyt26
Messages postés
3
Date d'inscription
dimanche 22 juin 2003
Statut
Membre
Dernière intervention
12 janvier 2006
12 janv. 2006 à 13:06
12 janv. 2006 à 13:06
Merci d'avance pour votre aide!!
