Dynamic SQL doubt - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Dynamic SQL doubt

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-11-14 : 05:32:18

Hi,

I have a Dynamic SQL stored procedure that allows user search orders/offers by specifying different parameters. Now this SP works fine, but I want to improve its performance in case the user does not specify some parameters. I mean, in this SP a sub query is executed every time the user search offers, but only is really necessary (this sub query) in case the user specify some value different to 0 in these parameters: @Quality_num, @Transport_cost, @Matur_num and @Pice. So, if these parameters are equal to 0 the sub query isn’ t necessary.

How could I write the SP so that the sub query only appears in those necessary cases?

Here is the SP: (The sub query is in bold)


USE market3
GO
ALTER PROCEDURE findOf_dyn
@Family smallint, @Product smallint, @Quality_num smallint,
@Transport_cost smallint, @Matur_num smallint, @Pice numeric(8,2), ...
As

BEGIN 
    SET NOCOUNT ON 
 
    Declare @sql nvarchar(4000), @param_list nvarchar(4000),
@sub_query nvarchar(4000)

    Select @sql = 'SELECT o.Offer_id, o.User_num, o.Offer_date,
o.Offer_title, p.Product_name, fam.Family_name ...

       From Offers As o

       LEFT JOIN Products_name As p   
       On o.Product_num = p.Product_Id
       LEFT JOIN Families_product As fam
       On p.Family_num = fam.Family_Id

              WHERE (1 = 1)'


    If @Family <> 0  
       SELECT @sql = @sql + ' AND (fam.Family_Id = @Family Or
(@Family = 9 AND o.Other_families IS NOT NULL))'

    If @Product <> 0  
       SELECT @sql = @sql + ' AND (o.Product_num = @Product)'


SET @sub_query = ' AND EXISTS (SELECT * FROM Offers_quality As of_qual 
                                   INNER JOIN Offers_maturation As of_mat
                                   on of_mat.Offer_qual_num = of_qual.Offer_qual_id
                                   WHERE (o.Offer_id = of_qual.Offer_num)'


SELECT @sql = @sql + @sub_query

If @Quality_num <> 0
   SELECT @sql = @sql + ' AND (of_qual.Quality_num = @Quality_num)' 


If @Transport_cost <> 0
   SELECT @sql = @sql + ' AND (of_qual.Transport_cost = @Transport_cost)'


If @Matur_num <> 0
  SELECT @sql = @sql + ' AND (of_mat.Matur_num = @Matur_num)'


If @Price <> 0
  SELECT @sql = @sql + ' AND (of_mat.Price <= @Price)'

SELECT @sql = @sql + ')'


    SELECT @sql = @sql + ' Order by o.Offer_date Desc, o.Offer_id Desc'  



    SELECT @param_list = '@Family smallint, @Product smallint,
@Quality_num smallint, @Transport_cost smallint, @Matur_num smallint,
@Pice numeric(8,2), ...'
    
    EXEC sp_executesql @sql, @param_list, @Family, @Product,
@Quality_num, @Transport_cost, @Matur_num, @Pice numeric, ...   

SET NOCOUNT OFF

END
GO

Thank you,
Cesar

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-11-14 : 06:20:22

u can put the subquery in an If construct
if <condition>
SET @sub_query = ' AND EXISTS (SELECT * FROM Offers_quality As of_qual
INNER JOIN Offers_maturation As of_mat
on of_mat.Offer_qual_num = of_qual.Offer_qual_id
WHERE (o.Offer_id = of_qual.Offer_num)'
else
SET @sub_query = ''

cesark
Posting Yak Master

215 Posts

Posted - 2005-11-14 : 06:47:42

If I could do this would be perfect, but how you would write your example into my real code posted? (Only put your example into my code)

Thanks

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-11-14 : 07:12:30

USE market3
GO
ALTER PROCEDURE findOf_dyn
@Family smallint, @Product smallint, @Quality_num smallint,
@Transport_cost smallint, @Matur_num smallint, @Pice numeric(8,2), ...
As

BEGIN
SET NOCOUNT ON

Declare @sql nvarchar(4000), @param_list nvarchar(4000),
@sub_query nvarchar(4000)

Select @sql = 'SELECT o.Offer_id, o.User_num, o.Offer_date,
o.Offer_title, p.Product_name, fam.Family_name ...

From Offers As o

LEFT JOIN Products_name As p
On o.Product_num = p.Product_Id
LEFT JOIN Families_product As fam
On p.Family_num = fam.Family_Id

WHERE (1 = 1)'

If @Family <> 0
SELECT @sql = @sql + ' AND (fam.Family_Id = @Family Or
(@Family = 9 AND o.Other_families IS NOT NULL))'

If @Product <> 0
SELECT @sql = @sql + ' AND (o.Product_num = @Product)'

if @Quality_num <> 0 --add the condition u want here
SET @sub_query = ' AND EXISTS (SELECT * FROM Offers_quality As of_qual
INNER JOIN Offers_maturation As of_mat
on of_mat.Offer_qual_num = of_qual.Offer_qual_id
WHERE (o.Offer_id = of_qual.Offer_num)'
else
SET @sub_query = ''

SELECT @sql = @sql + @sub_query

If @Quality_num <> 0
SELECT @sql = @sql + ' AND (of_qual.Quality_num = @Quality_num)'

If @Transport_cost <> 0
SELECT @sql = @sql + ' AND (of_qual.Transport_cost = @Transport_cost)'

If @Matur_num <> 0
SELECT @sql = @sql + ' AND (of_mat.Matur_num = @Matur_num)'

If @Price <> 0
SELECT @sql = @sql + ' AND (of_mat.Price <= @Price)'

SELECT @sql = @sql + ')'

SELECT @sql = @sql + ' Order by o.Offer_date Desc, o.Offer_id Desc'

SELECT @param_list = '@Family smallint, @Product smallint,
@Quality_num smallint, @Transport_cost smallint, @Matur_num smallint,
@Pice numeric(8,2), ...'

EXEC sp_executesql @sql, @param_list, @Family, @Product,
@Quality_num, @Transport_cost, @Matur_num, @Pice numeric, ...

SET NOCOUNT OFF

END
GO

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2005-11-14 : 07:32:05

USE market3
GO
ALTER PROCEDURE findOf_dyn
@Family smallint, @Product smallint, @Quality_num smallint,
@Transport_cost smallint, @Matur_num smallint, @Pice numeric(8,2), ...
As

BEGIN
SET NOCOUNT ON

Declare @sql nvarchar(4000), @param_list nvarchar(4000),
@sub_query nvarchar(4000)

Select @sql = 'SELECT o.Offer_id, o.User_num, o.Offer_date,
o.Offer_title, p.Product_name, fam.Family_name ...

From Offers As o

LEFT JOIN Products_name As p
On o.Product_num = p.Product_Id
LEFT JOIN Families_product As fam
On p.Family_num = fam.Family_Id

WHERE (1 = 1)'

If @Family <> 0
SELECT @sql = @sql + ' AND (fam.Family_Id = @Family Or
(@Family = 9 AND o.Other_families IS NOT NULL))'

If @Product <> 0
SELECT @sql = @sql + ' AND (o.Product_num = @Product)'

if @Quality_num <> 0 --add the condition u want here
SET @sub_query = ' AND EXISTS (SELECT * FROM Offers_quality As of_qual
INNER JOIN Offers_maturation As of_mat
on of_mat.Offer_qual_num = of_qual.Offer_qual_id
WHERE (o.Offer_id = of_qual.Offer_num)'
else
SET @sub_query = ''

SELECT @sql = @sql + @sub_query
if @sub_query <> ''
begin
If @Quality_num <> 0
SELECT @sql = @sql + ' AND (of_qual.Quality_num = @Quality_num)'

If @Transport_cost <> 0
SELECT @sql = @sql + ' AND (of_qual.Transport_cost = @Transport_cost)'

If @Matur_num <> 0
SELECT @sql = @sql + ' AND (of_mat.Matur_num = @Matur_num)'

If @Price <> 0
SELECT @sql = @sql + ' AND (of_mat.Price <= @Price)'

SELECT @sql = @sql + ')'
end
SELECT @sql = @sql + ' Order by o.Offer_date Desc, o.Offer_id Desc'

SELECT @param_list = '@Family smallint, @Product smallint,
@Quality_num smallint, @Transport_cost smallint, @Matur_num smallint,
@Pice numeric(8,2), ...'

EXEC sp_executesql @sql, @param_list, @Family, @Product,
@Quality_num, @Transport_cost, @Matur_num, @Pice numeric, ...

SET NOCOUNT OFF

END
GO

cesark
Posting Yak Master

215 Posts

Posted - 2005-11-14 : 11:04:19

Yeah

it' s a very good way to do it, it works and seems very fast in performance!

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources