samyt26
|
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
|