det ar nog vad jag gor, hur slulle du rekomendera denna kod:
CREATE PROCEDURE dbo.sp_ProductSearch3
@searchstring1 varchar(100),
@searchstring2 varchar(100),
@searchstring3 varchar(1),
@searchstring4 varchar(10)
AS
DECLARE
@count int,
@currentid int,
@currentword varchar(100),
@searchword varchar(100),
@QryString varchar(500),
@beginprice int,
@endprice int,
@strSql varchar(100)
set nocount on
/*************************/
if @searchstring3 = '1'
begin
select @beginprice = 0
select @endprice = 249
end
if @searchstring3 = '2'
begin
select @beginprice = 250
select @endprice = 499
end
if @searchstring3 = '3'
begin
select @beginprice = 500
select @endprice = 999
end
if @searchstring3 = '4'
begin
select @beginprice = 1000
select @endprice = 100000
end
/*********************/
if @searchstring1 = ''
begin
select @searchstring1 = ' '
end
/* Store customer search requests */
if @searchstring1 <> ' '
begin
insert into SearchRequest (searchfor, searchdate)
values (@searchstring1, getdate())
end
/******************************************************************************************************************************/
/* For Testing purposes */
/* select @searchstring1 = ''
select @beginprice = 1000
select @endprice = 100000
select @searchstring2 = '0'
*/
/*****************************************************************************************************************************/
create table #temp (
tempid int,
searchword varchar(30)
)
select @count = 1
if CHARINDEX(' ',@searchstring1) = 0
begin
insert into #temp (tempid, searchword)
values (@count, @searchstring1)
end
WHILE (select CHARINDEX(' ',@searchstring1)) <> 0
BEGIN
select @searchword = substring(@searchstring1,1,CHARINDEX(' ',@searchstring1))
insert into #temp (tempid, searchword)
values (@count, @searchword)
select @count = @count + 1
select @searchstring1 = rtrim(ltrim(substring(@searchstring1,charindex(' ',@searchstring1),len(@searchstring1))))
if charindex(' ',@searchstring1) = 0
begin
insert into #temp (tempid, searchword)
values (@count, @searchstring1)
BREAK
end
CONTINUE
END
create table #productlist (
imagename varchar(15),
productnumber varchar(15),
productname varchar(256),
productdescription varchar(2048),
departmentid varchar(5),
categoryid varchar(5),
sellprice money,
ok_fl varchar(1),
AlsoIn varchar(1000)
)
while @count <> 0
begin
select @currentid = @count
if exists(select * from #temp where tempid = @currentid)
begin
select @currentword = '%'+ltrim(rtrim((select searchword from #temp where tempid = @currentid)))+'%'
insert into #productlist
select replace(replace(replace(i.ProductNumber,'#',''),'/',''),'-',''), i.ProductNumber,ProductName, ProductDescription, DepartmentID, CategoryID, SellPrice, 'y', AlsoIn
from inventory i, ProductAlsoAvailableIn am
where i.ProductNumber not in (select productnumber from #productlist)
/* and (productname like @currentword */
and (i.ProductNumber like @currentword
or productdescription like @currentword
or productName like @currentword)
and SellPrice is not null
and Discontinued = 0
and i.productnumber = am.productnumber
order by SellPrice
end
select @count = @count - 1
continue
end
set @strSql = 'delete from #productlist '
set @strSql = @strSql + 'where categoryid not in ' + @searchstring2
if len(@searchstring2) > 0
begin
exec(@strSql)
end
/*
if @searchstring2 <> 0 and @searchstring2 is not null
begin
delete from #productlist
where categoryid <> @searchstring2
end
*/
if @searchstring3 <> '0' and @searchstring3 <> ''
begin
delete from #productlist
where SellPrice not between @beginprice and @endprice
end
if @searchstring4 <> 0 and @searchstring4 is not null
begin
delete from #productlist
where departmentid <> @searchstring4
end
select * from #productlist
GO
