SQL Server Performance Forum – Threads Archive

Dynamic SQL doubt

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#%92 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 :confused:

ffer_id, o.User_num, :confused:

ffer_date, :confused:

ffer_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
:confused:

ther_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 (ffer_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 :confused:

ffer_date Desc, :confused:

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 :confused:

ffer_id, o.User_num, :confused:

ffer_date, :confused:

ffer_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
:confused:

ther_families IS NOT NULL))’
If @Product <> 0 SELECT @sql = @sql + ‘ AND (o.Product_num = @Product)’
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 :confused:

ffer_date Desc, :confused:

ffer_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
IF @Quality_num = 0 and @Transport_cost = 0 and @Matur_num = 0 and @Pice = 0
Begin
select @sql = Replace (@sql, ‘WHERE #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 ( :confused:

ffer_id = of_qual.Offer_num)’)
End
Else
Begin
select @str1 = Replace (@str1, ‘WHERE #SUB_QUERY#’, ‘ ‘)
End HTH.
Surendra Kalekar

Sorry. If statement must be above
EXEC sp_executesql @sql, @param_list, @Family, @Product, @Quality_num,
@Transport_cost, @Matur_num, @Pice numeric, … SET NOCOUNT OFF END
GO Surendra Kalekar

..looks good [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />]. Now I have to go but in one hour I am going to test it Thanks

I receive an error in QA:
incorrect syntax near of ‘#SUB_QUERY#’. What is wrong?

And, what about this?: 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 :confused:

ffer_id, o.User_num, :confused:

ffer_date, :confused:

ffer_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 :confused:

ther_families IS NOT NULL))’ If @Product <> 0
SELECT @sql = @sql + ‘ AND (o.Product_num = @Product)’
If @Quality_num <> 0 Or @Transport_cost <> 0 Or @Matur_num <> 0 Or @Price <> 0
BEGIN
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 (ffer_id = of_qual.Offer_num)’ SELECT @sql = @sql + @sub_query
END 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)’ If @sub_query <> ”
BEGIN
SELECT @sql = @sql + ‘)’
END SELECT @sql = @sql + ‘ Order by :confused:

ffer_date Desc, :confused:

This kind of instruction ‘#some_query#’ exists in T-Sql?

Haven’t looked at this in detail, but I think the idea is that you insert a proper subquery instead of ‘#some_query#’.

quote:Originally posted by Adriaan Haven’t looked at this in detail, but I think the idea is that you insert a proper subquery instead of ‘#some_query#’.

Yes, Cesar
It is throwing you error because you might running this before replacing the keyword #SUBQUERY# with the actual subquery statement. Surendra Kalekar

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by Cesar And, what about this?: <pre id="code"> 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 :confused:

ffer_id, o.User_num, :confused:

ffer_date, :confused:

ffer_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 :confused:

ther_families IS NOT NULL))' If @Product <> 0 SELECT @sql = @sql + ‘ AND (o.Product_num = @Product)' If @Quality_num <> 0 Or @Transport_cost <> 0 Or @Matur_num <> 0 Or @Price <> 0 BEGIN 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 ( :confused:

ffer_id = of_qual.Offer_num)' SELECT @sql = @sql + @sub_query END 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)' If @sub_query <> ” BEGIN SELECT @sql = @sql + ‘)' END SELECT @sql = @sql + ‘ Order by :confused:

ffer_date Desc, :confused:

ffer_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</pre id="code"> Do you think it#%92 s a better solution? <hr height="1" noshade id="quote"></blockquote id="quote"> You can use this if you are not comfortable with replace #subquery# idea. Remember you are creating dynamic sql and not executing so any logic, which will create proper sql, will be known as better solution [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />]. <h6>Surendra Kalekar</h6>

Ok, thanks

]]>