5 replies to this topic

[Abbath1349]

I've written stored procedure fro advanced search but it's very big.How I can less this t-SQL code?

ALTER PROCEDURE [dbo].[AdvancedSearch]

@Min_Price money,

@Max_price money,

@Product_name nvarchar(35),

@Producer nvarchar(25),

@Category nvarchar(25)

AS


DECLARE @MAXIMAL_PRICE money

DECLARE @MINIMAL_PRICE money

SET @MAXIMAL_PRICE=(SELECT MAX([Цена(Руб.)]) FROM [Каталог Товаров])

SET @MINIMAL_PRICE=(SELECT MIN([Цена(Руб.)]) FROM [Каталог Товаров])


IF(@Min_Price IS NULL )

SET @Min_Price=@MINIMAL_PRICE

IF(@Max_price IS NULL)

SET @Max_price=@MAXIMAL_PRICE


IF(@Product_name IS NULL AND @Producer IS NULL AND @Category IS NULL)

BEGIN

SELECT [Наименование Товара],[Цена(Руб.)]

FROM [Каталог Товаров]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price

END


ELSE IF(@Product_name IS NULL AND @Producer IS NULL AND @Category IS NOT NULL)

BEGIN

SELECT [Наименование Товара],[Сертификат Качества],Производитель,[Цена(Руб.)]

FROM [Каталог Товаров]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price AND Категория=@Category

END


ELSE IF(@Product_name IS NULL AND @Producer IS NOT NULL AND @Category IS NULL)

BEGIN

SELECT [Наименование Товара],[Сертификат Качества],Производитель,[Цена(Руб.)]

FROM [Каталог Товаров]

JOIN 

Поставщики

ON [Каталог Товаров].Поставщик=Поставщики.[Код Поставщика]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price AND [Имя Поставщика]=@Producer

END


ELSE IF(@Product_name IS NULL AND @Producer IS NOT NULL AND @Category IS NOT NULL)

BEGIN

SELECT [Наименование Товара],[Сертификат Качества],Производитель,[Цена(Руб.)]

FROM [Каталог Товаров]

JOIN 

Поставщики

ON [Каталог Товаров].Поставщик=Поставщики.[Код Поставщика]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price

AND [Имя Поставщика]=@Producer AND Категория=@Category

END


ELSE IF(@Product_name IS NOT NULL AND @Producer IS NULL AND @Category IS NULL)

BEGIN

SELECT  [Наименование Товара],[Сертификат Качества],Производитель,[Цена(Руб.)]

FROM [Каталог Товаров]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price

AND [Наименование Товара] Like '%'+@Product_name+'%'

END


ELSE IF(@Product_name IS NOT NULL AND @Producer IS NULL AND @Category IS NOT NULL)

BEGIN

SELECT  [Наименование Товара],[Сертификат Качества],Производитель,[Цена(Руб.)]

FROM [Каталог Товаров]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price

AND [Наименование Товара] Like '%'+@Product_name+'%' AND Категория=@Category

END


ELSE IF(@Product_name IS NOT NULL AND @Producer IS NOT NULL AND @Category IS NULL)

BEGIN

SELECT [Наименование Товара],[Сертификат Качества],Производитель,[Цена(Руб.)]

FROM [Каталог Товаров]

JOIN 

Поставщики

ON [Каталог Товаров].Поставщик=Поставщики.[Код Поставщика]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price

AND [Имя Поставщика]=@Producer AND [Наименование Товара] Like '%'+@Product_name+'%'

END


ELSE IF(@Product_name IS NOT NULL AND @Producer IS NOT NULL AND @Category IS NOT NULL)

BEGIN

SELECT [Наименование Товара],[Сертификат Качества],Производитель,[Цена(Руб.)]

FROM [Каталог Товаров]

JOIN 

Поставщики

ON [Каталог Товаров].Поставщик=Поставщики.[Код Поставщика]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price AND Категория=@Category

AND [Имя Поставщика]=@Producer AND [Наименование Товара] Like '%'+@Product_name+'%'

END

[WingedPanther]

In what sense is it big? Compared to some I've seen, it's quite small?

Is there a reason you're doing this instead of a select statement with case clauses?

[Abbath1349]

Imean: This procedure has a lot of similar sections. Maybe there is a way to reduce code?:confused:

[WingedPanther]

Unfortunately, the Cyrillic makes it hard for me to determine how similar they really are. T-SQL isn't always elegant, but it is powerful.

[Abbath1349]

So I correct code I've written Latin insteed Cyrilic.:) There are 3 variables @Product_name nvarchar(35),
@Provider nvarchar(25),
@Category nvarchar(25)
and different combination of them. Like "Product_name IS NOT NULL AND @Producer IS NOT NULL AND @Category IS NOT NULL"

ALTER PROCEDURE [dbo].[AdvancedSearch]

@Min_Price smallmoney,

@Max_price smallmoney,

@Product_name nvarchar(35),

@Provider nvarchar(25),

@Category nvarchar(25)

AS


DECLARE @MAXIMAL_PRICE money

DECLARE @MINIMAL_PRICE money

SET @MAXIMAL_PRICE=(SELECT MAX([Цена(Руб.)]) FROM [Каталог Товаров])

SET @MINIMAL_PRICE=(SELECT MIN([Цена(Руб.)]) FROM [Каталог Товаров])


IF(@Min_Price IS NULL )

SET @Min_Price=@MINIMAL_PRICE

IF(@Max_price IS NULL)

SET @Max_price=@MAXIMAL_PRICE


IF(@Product_name IS NULL AND @Provider IS NULL AND @Category IS NULL)

BEGIN

SELECT [Product Name],Producer,

[Provider Name],

CAST((CAST([Cost(Rub.)] as numeric(18,2))) as nvarchar(12))+' Rub.' AS 'Cost'

FROM dbo.[Catalog]

JOIN 

Providers

ON [Catalog].Provider=Providers.[Proveder code]

WHERE [Cost(Rub.)] BETWEEN @Min_Price AND @Max_price

END


ELSE IF(@Product_name IS NULL AND @Provider IS NULL AND @Category IS NOT NULL)

BEGIN

SELECT [Product Name],Producer,

[Provider Name],

CAST((CAST([Cost(Rub.)] as numeric(18,2))) as nvarchar(12))+' Rub.' AS 'Cost'

FROM dbo.[Catalog]

JOIN 

Providers

ON [Catalog].Provider=Providers.[Proveder code]

WHERE [Cost(Rub.)] BETWEEN @Min_Price AND @Max_price AND Категория=@Category

END


ELSE IF(@Product_name IS NULL AND @Provider IS NOT NULL AND @Category IS NULL)

BEGIN

SELECT [Product Name],Producer,

[Provider Name],

CAST((CAST([Cost(Rub.)] as numeric(18,2))) as nvarchar(12))+' Rub.' AS 'Cost'

FROM dbo.[Catalog]

JOIN 

Providers

ON [Catalog].Provider=Providers.[Proveder code]

WHERE [Cost(Rub.)] BETWEEN @Min_Price AND @Max_price AND 

[Provider Name]=@Provider

END


ELSE IF(@Product_name IS NULL AND @Provider IS NOT NULL AND @Category IS NOT NULL)

BEGIN

SELECT [Product Name],Producer,

[Provider Name],

CAST((CAST([Cost(Rub.)] as numeric(18,2))) as nvarchar(12))+' Rub.' AS 'Cost'

FROM dbo.[Catalog]

JOIN 

Providers

ON [Catalog].Provider=Providers.[Proveder code]

WHERE [Cost(Cost.)] BETWEEN @Min_Price AND @Max_price

AND [Provider Name]=@Provider AND Category=@Category

END


ELSE IF(@Product_name IS NOT NULL AND @Provider IS NULL AND @Category IS NULL)

BEGIN

SELECT [Product Name],Producer,

[Provider Name],

CAST((CAST([Cost(Rub.)] as numeric(18,2))) as nvarchar(12))+' Rub.' AS 'Cost'

FROM dbo.[Catalog]

JOIN 

Providers

ON [Catalog].Provider=Providers.[Proveder code]

WHERE [Цена(Руб.)] BETWEEN @Min_Price AND @Max_price

AND [Product Name] Like '%'+@Product_name+'%'

END


ELSE IF(@Product_name IS NOT NULL AND @Provider IS NULL AND @Category IS NOT NULL)

BEGIN

SELECT [Product Name],Producer,

[Provider Name],

CAST((CAST([Cost(Rub.)] as numeric(18,2))) as nvarchar(12))+' Rub.' AS 'Cost'

FROM dbo.[Catalog]

JOIN 

Providers

ON [Catalog].Provider=Providers.[Proveder code]

WHERE [Cost(Rub.)] BETWEEN @Min_Price AND @Max_price

AND [Product Name] Like '%'+@Product_name+'%' AND Category=@Category

END


ELSE IF(@Product_name IS NOT NULL AND @Provider IS NOT NULL AND @Category IS NULL)

BEGIN

SELECT [Product Name],Producer,

[Provider Name],

CAST((CAST([Cost(Rub.)] as numeric(18,2))) as nvarchar(12))+' Rub.' AS 'Cost'

FROM dbo.[Catalog]

JOIN 

Providers

ON [Catalog].Provider=Providers.[Proveder code]

WHERE [Cost(Rub.)] BETWEEN @Min_Price AND @Max_price

AND [Provider Name]=@Provider AND [Product Name] Like '%'+@Product_name+'%'

END


ELSE IF(@Product_name IS NOT NULL AND @Provider IS NOT NULL AND @Category IS NOT NULL)

BEGIN

SELECT [Product Name],Producer,

[Provider Name],

CAST((CAST([Cost(Rub.)] as numeric(18,2))) as nvarchar(12))+' Rub.' AS 'Cost'

FROM dbo.[Catalog]

JOIN 

Providers

ON [Catalog].Provider=Providers.[Proveder code]

WHERE [Cost(Rub.)] BETWEEN @Min_Price AND @Max_price AND Category=@Category

AND [Provider Name]=@Provider AND [Product Name] Like '%'+@Product_name+'%'

END

[WingedPanther]

Short of assembling the SQL where clauses piecemeal, and then running the result, I don't think so. There's also a question as to how much you will lose in clarity by doing it that way.