SQL Server Performance

Starting with Dynamic SQL (Select problem)

Discussion in 'T-SQL Performance Tuning for Developers' started by Cesar, Oct 13, 2005.

  1. Cesar New Member

    I have a dynamic SQL SP that its ‘Select#%92 statement stars thus:


    Select @sql =
    'Declare @offer_date As datetime, @Id As bigint
    SELECT @offer_date = offe.offer_date, @Id = offe.Offer_id
    From Offers As offe

    ...


    And I receive an error from app that says: You must declare the variable ‘@offer_date#%92. And I already declared it.. What is wrong?

    Thanks
  2. ranjitjain New Member

    Hi Cesar,
    When you declare any variable inside dynamic statement its not accesible after executing the code.
    To access that you need to use sp_executesql and then with output parameter
  3. Madhivanan Moderator

  4. manojvjti New Member



    Dynamic SQL is used if your are querying tables on a fly & your not sure which column,tables are going to be involved. It is like one command & any variable declared in dynamic sql can be used only inside the dynamic sql query.

    You can declare the variable in the SP & then pass only the values to dynamic SQL.
  5. ranjitjain New Member

    Cesar,
    Consider this example:

    declare @statement nvarchar(1000)
    declare @Top5Sum int

    set @statement=N'select @Top5Sum=sum(bytes) from '+@tempTable

    exec sp_executesql @statement,N'@Top5Sum int OUTPUT',@Top5Sum OUTPUT

    select @Top5Sum
  6. FrankKalis Moderator

    Check out SQL Server MVP Erland Sommarskog's in-depth article on the topic. It should answer almost all questions.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  7. Cesar New Member

    Thank you ranjitjain, I tried to do what you said but I can#%92 t achieve it.

    Madhivanan, you recommended to me a very interesting article in this post:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10712 . And after read it I though it was the best option in that case.
    Thank you again for this another interesting article!


    Here is the structure of my SP to find first row of results: (After that, I have more code to get all rows and return a specified number of records per page to web application)



    USE market3
    GO
    ALTER PROCEDURE findOf_dyn
    @CurrentPage int, @PageSize int, @Family smallint, @Product smallint, ..
    As

    BEGIN
    SET NOCOUNT ON

    Declare @sql nvarchar(4000), @param_list nvarchar(4000),
    @Max_row int, @First_row int

    SET @Max_row = @PageSize * @CurrentPage
    SET @First_row = @Max_row - (@PageSize - 1)



    SET ROWCOUNT @First_row

    Select @sql =
    'Declare @Offer_date As datetime, @Id As bigint
    SELECT @Offer_date = offe.Offer_date, @Id = offe.Offer_id
    From Offers As offe

    LEFT JOIN Products_name As prod
    On offe.Product_num = prod.Product_Id
    LEFT JOIN Families_product As fam
    On prod.Family_num = fam.Family_Id
    LEFT JOIN Offers_quality As of_qual
    On offe.Offer_id = of_qual.Offer_num
    LEFT JOIN Offers_rippening As of_rip
    On of_qual.Offer_qual_id = of_rip.Offer_qual_num

    WHERE 1 = 1'


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

    If @Product <> 0
    SELECT @sql = @sql + ' AND offe.Product_num = @xProduct'





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

    SELECT @param_list = ' @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, ...'

    EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, …


    ...


    What I am trying to accomplish here is (among others) set @Offer_date = offe.Offer_date and @Id = offe.Offer_id in this dynamic SQL Select:


    ...
    Select @sql =
    'Declare @Offer_date As datetime, @Id As bigint
    SELECT @Offer_date = offe.Offer_date, @Id = offe.Offer_id
    From Offers As offe

    LEFT JOIN Products_name As prod
    ...


    In order to use these variables later (in the same SP) here (in bold):


    SET ROWCOUNT @PageSize

    Select @sql =
    'SELECT offe.Offer_id, offe.User_num, offe.Offer_date, offe.Offer_title, ...

    From Offers As offe

    LEFT JOIN Products_name As prod
    On offe.Product_num = prod.Product_Id
    LEFT JOIN Families_product As fam
    On prod.Family_num = fam.Family_Id
    LEFT JOIN Offers_quality As of_qual
    On offe.Offer_id = of_qual.Offer_num
    LEFT JOIN Offers_rippening As of_rip
    On of_qual.Offer_qual_id = of_rip.Offer_qual_num


    WHERE 1 = 1 AND ((offe.Offer_date < @Offer_date) Or (offe.Offer_date = @Offer_date And offe.Offer_id <= @Id))'

    If @Family <> 0
    SELECT @sql...
    ...


    How can I do it?
  8. Cesar New Member

    I think the solution is this: (In bold)<br /><br />At least none error is returned from app.. [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />USE market3<br />GO<br />ALTER PROCEDURE findOf_dyn<br />@CurrentPage int, @PageSize int, @Family smallint, @Product smallint, ..<br />As<br /><br />BEGIN <br /> SET NOCOUNT ON <br /> <br /> Declare @sql nvarchar(4000), @param_list nvarchar(4000), <br /> @Max_row int, @First_row int<br /><br /> SET @Max_row = @PageSize * @CurrentPage <br /> SET @First_row = @Max_row - (@PageSize - 1) <br /><br /><br /><br /> SET ROWCOUNT @First_row <br /><br /> <b>Declare @Offer_date nvarchar(1000), @Id nvarchar(1000)</b> <br /><br /> Select @sql = <br /> 'SELECT#%92 <b>+ @Offer_date +</b> ‘= offe.Offer_date,#%92 <b>+ @Id +</b> ‘= offe.Offer_id<br /> From Offers As offe<br /><br /> LEFT JOIN Products_name As prod <br /> On offe.Product_num = prod.Product_Id<br /> LEFT JOIN Families_product As fam<br /> On prod.Family_num = fam.Family_Id<br /> LEFT JOIN Offers_quality As of_qual <br /> On offe.Offer_id = of_qual.Offer_num <br /> LEFT JOIN Offers_rippening As of_rip <br /> On of_qual.Offer_qual_id = of_rip.Offer_qual_num <br /><br /> WHERE 1 = 1'<br /><br /><br /> If @Family &lt;&gt; 0 <br /> SELECT @sql = @sql + ' AND (fam.Family_Id = @xFamily Or (x@Family = 9 AND offe.Other_families IS NOT NULL))'<br /><br /> If @Product &lt;&gt; 0 <br /> SELECT @sql = @sql + ' AND offe.Product_num = @xProduct'<br /><br /><br /><br /> <br /><br /> SELECT @sql = @sql + ' Order by offe.Offer_date Desc, offe.Offer_id Desc' <br /><br /> SELECT @param_list = ' @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, <b>@Offer_date nvarchar(1000) output, @Id nvarchar(1000) output</b> ...'<br /> <br /> EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, <b>@Offer_date output, @Id output</b> <br /><br />...<br /></font id="code"></pre id="code"><br /><br />And later:<br /><pre id="code"><font face="courier" size="2" id="code"><br />...<br /> WHERE 1 = 1 AND ((offe.Offer_date &lt;#%92<b>+ @Offer_date +</b>#%92) Or (offe.Offer_date =#%92 <b>+ @Offer_date +</b>#%92 And offe.Offer_id &lt;=#%92 <b>+ @Id +</b>#%92))'<br /></font id="code"></pre id="code"><br /><br />The problem now is none record is returned in any case
  9. Madhivanan Moderator

    Why did you use nvarchar for Date?

    Use this Declaration

    Declare @Offer_date DateTime, @Id nvarchar(1000)

    Madhivanan

    Failing to plan is Planning to fail
  10. Cesar New Member


    Because if I declare @Offer_date as datetime, thus:


    Declare @Offer_date datetime, @Id nvarchar(1000)

    Select @sql =
    'SELECT#%92 + @Offer_date + ‘= offe.Offer_date,#%92 + @Id + ‘= offe.Offer_id
    From Offers As offe


    I receive an error from application that says: error when convert a string to datetime. I suppose this is because all code generated inside @sql parameter becomes a string (is a string).



  11. Cesar New Member

    I see that it lacked (SELECT @Offer_date, @Id) after (EXEC sp_executesql), thus:<br /><pre id="code"><font face="courier" size="2" id="code"><br />EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @Offer_date output, @Id output<br /><br /><b>SELECT @Offer_date, @Id</b><br /></font id="code"></pre id="code"><br /><br />And now I receive next error [<img src='/community/emoticons/emotion-5.gif' alt=';)' />] <br /><br /><br />Here is my complete SP:<br />It#%92 s a SP for paging through results <br /><pre id="code"><font face="courier" size="2" id="code"><br />USE market3<br />GO<br />ALTER PROCEDURE findOf_dyn<br />@CurrentPage int, @PageSize int, @Family smallint, @Product smallint, @TotalRecords nvarchar(100) output<br />As<br /><br />BEGIN <br /> SET NOCOUNT ON <br /> <br /> Declare @sql nvarchar(4000), @param_list nvarchar(4000), <br /> @Max_row int, @First_row int<br /><br /> SET @Max_row = @PageSize * @CurrentPage <br /> SET @First_row = @Max_row - (@PageSize - 1) <br /><br /><br /><br /><br /> Declare @Offer_date datetime, @Id nvarchar(1000) <br /><br /> Select @sql = <br /> 'SET ROWCOUNT#%92 + convert(nvarchar(100), @First_row) + ‘ <br /> SELECT#%92 + convert(nvarchar(100), @Offer_date) + ‘= offe.Offer_date,#%92 + @Id + ‘= offe.Offer_id<br /> From Offers As offe<br /><br /> LEFT JOIN Products_name As prod <br /> On offe.Product_num = prod.Product_Id<br /> LEFT JOIN Families_product As fam<br /> On prod.Family_num = fam.Family_Id<br /> LEFT JOIN Offers_quality As of_qual <br /> On offe.Offer_id = of_qual.Offer_num <br /> LEFT JOIN Offers_rippening As of_rip <br /> On of_qual.Offer_qual_id = of_rip.Offer_qual_num <br /><br /> WHERE 1 = 1'<br /><br /><br /> If @Family &lt;&gt; 0 <br /> SELECT @sql = @sql + ' AND (fam.Family_Id = @xFamily Or (x@Family = 9 AND offe.Other_families IS NOT NULL))'<br /><br /> If @Product &lt;&gt; 0 <br /> SELECT @sql = @sql + ' AND offe.Product_num = @xProduct'<br /><br /><br /><br /> <br /><br /> SELECT @sql = @sql + ' Order by offe.Offer_date Desc, offe.Offer_id Desc' <br /><br /> SELECT @param_list = ' @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, @Offer_date datetime output, @Id nvarchar(1000) output'<br /> <br /> EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @Offer_date output, @Id output <br /><br />SELECT @Offer_date, @Id<br /><br /><br /><br /><br /><br /><br /> Select @sql =<br /> 'SET ROWCOUNT#%92 + convert(nvarchar(100), @PageSize) + ‘<br /> SELECT offe.Offer_id, offe.User_num, offe.Offer_date, offe.Offer_title<br /> <br /> From Offers As offe <br /><br /> LEFT JOIN Products_name As prod <br /> On offe.Product_num = prod.Product_Id<br /> LEFT JOIN Families_product As fam<br /> On prod.Family_num = fam.Family_Id<br /> LEFT JOIN Offers_quality As of_qual <br /> On offe.Offer_id = of_qual.Offer_num <br /> LEFT JOIN Offers_rippening As of_rip <br /> On of_qual.Offer_qual_id = of_rip.Offer_qual_num<br /><br />WHERE 1 = 1 AND ((offe.Offer_date &lt;#%92+ @Offer_date +#%92) Or (offe.Offer_date =#%92 + @Offer_date +#%92 And offe.Offer_id &lt;=#%92 + @Id +#%92))'<br /><br /><br />If @Family &lt;&gt; 0 <br /> SELECT @sql = @sql + ' AND (fam.Family_Id = @xFamily Or (x@Family = 9 AND offe.Other_families IS NOT NULL))'<br /><br /> If @Product &lt;&gt; 0 <br /> SELECT @sql = @sql + ' AND offe.Product_num = @xProduct'<br /><br /><br /><br /> SELECT @sql = @sql + ' Order by offe.Offer_date Desc, offe.Offer_id Desc' <br /> SELECT @sql = @sql + ' SET ROWCOUNT 0'<br /><br /> SELECT @param_list = ' @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, @TotalRecords nvarchar(100) output'<br /> <br /> EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @TotalRecords output <br /><br /><br /><br /><br /><br />SELECT @sql =<br /> 'SELECT' + @TotalRecords + '= COUNT(*)<br /><br />From Offers As offe <br /><br /> LEFT JOIN Products_name As prod <br /> On offe.Product_num = prod.Product_Id<br /> LEFT JOIN Families_product As fam<br /> On prod.Family_num = fam.Family_Id<br /> LEFT JOIN Offers_quality As of_qual <br /> On offe.Offer_id = of_qual.Offer_num <br /> LEFT JOIN Offers_rippening As of_rip <br /> On of_qual.Offer_qual_id = of_rip.Offer_qual_num<br /><br />WHERE 1 = 1#%92<br /><br /><br />If @Family &lt;&gt; 0 <br /> SELECT @sql = @sql + ' AND (fam.Family_Id = @xFamily Or (x@Family = 9 AND offe.Other_families IS NOT NULL))'<br /><br /> If @Product &lt;&gt; 0 <br /> SELECT @sql = @sql + ' AND offe.Product_num = @xProduct'<br /><br /><br /><br /> SELECT @param_list = ' @xCurrentPage int, @xPageSize int, @xFamily smallint, @xProduct smallint, @TotalRecords nvarchar(100) output '<br /> <br /> EXEC sp_executesql @sql, @param_list, @CurrentPage, @PageSize, @Family, @Product, @TotalRecords output <br /><br /> SELECT @TotalRecords<br /><br /><br />SET NOCOUNT OFF <br />END <br />GO<br /><br /></font id="code"></pre id="code"><br /><br /><br />Somebody sees something wrong?
  12. Madhivanan Moderator

    >>And now I receive next error

    What is the error?

    Madhivanan

    Failing to plan is Planning to fail
  13. Cesar New Member

    Well, indeed no error appears, simply none record is returned in any case
  14. satya Moderator

Share This Page