SQL Server Performance

How do you construct a Dynamic Stored Procedure with more than 8000 chars

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by Steven Mie, Sep 11, 2007.

  1. Steven Mie New Member

    My problem (excuse me for being a novice) is that whenever the query becomes complex
    in the @whereclause (and it does) it cant exceed the character limit of a nvarchar. Now if someone could shed some ligfht on this for me I would be
    incredibly grateful. I have tried doing the exec(@var1 + @var2) but this did not seem to work.
    I have included the stored procedure below.
    ALTER
    PROCEDURE [dbo].[SearchTenderMultiRegions]
    (
    @whereclause nVarChar(MAX), --4000 chars
    @PageIndex int,
    @PageSize int
    ) ASSET
    NOCOUNT ON;DECLARE @sql nvarChar(MAX)
    SET @sql= 'SELECT * FROM (SELECT DISTINCT TOP (' + CONVERT(nvarchar(10),(@PageIndex*@PageSize))
    SET @sql= @sql + ') ROW_NUMBER() OVER (ORDER BY Tenders.ID DESC)AS Row, Tenders.ID,Tenders.Title,Address.Suburb,Tenders.ClosingDateTime,Tenders.IsApproved,Tenders.TendersSourceID, Tenders.SourceDate,
    UserTracking.CreateUserID,UserTracking.CreateDateTime FROM Tenders LEFT JOIN TenderContact ON Tenders.ID = TenderContact.TendersID
    LEFT JOIN TenderCategory ON Tenders.ID = TenderCategory.TendersID
    LEFT JOIN TenderRegion ON Tenders.ID = TenderRegion.TendersID
    LEFT JOIN RegionStateCountry ON TenderRegion.RegionStateCountryID = RegionStateCountry.ID
    LEFT JOIN Address ON Tenders.ID = Address.TendersID
    LEFT JOIN UserTracking ON Tenders.ID = UserTracking.TendersID
    WHERE 'SET
    @sql= @sql + @whereclause
    SET @sql = @sql + ') as TenderSearchEntries
    WHERE Row between ('

    SET @sql= @sql + CONVERT(nvarchar(10), @PageIndex)
    SET @sql= @sql + ' - 1) * '
    SET @sql= @sql + CONVERT(nvarchar(10), @PageSize)
    SET @sql= @sql + '+ 1 and '
    SET @sql= @sql + CONVERT(nvarchar(10), @PageIndex)
    SET @sql= @sql + ' * '
    SET @sql= @sql + CONVERT(nvarchar(10), @PageSize)EXEC
    (@sql)
  2. chopeen Member

    You can use table aliases to shorten your query (all Tenders.ID will become T.ID for example), but one day you will reach the limit again.
    You can put entire FROM clause in a view, so the FROM clause in this query will become FROM dbo.VIEW_name_of_the_view.
    What version of SQL Server are you running?

  3. Steven Mie New Member

    SQL Server 2005
  4. Adriaan New Member

    If you have a client app that is compiling the WHERE clause and all that, you might look into building the whole query in the client app, and just executing an ad-hoc query over ADO (or whatever interface you're using). - Then again, of course you should be using sprocs.
    Another option is to feed the filter values to a holding table, then do an INNER JOIN on that table to filter.
  5. chopeen Member

    I don't understand one thing - you use nvarchar(max), so when exactly do you hit the 8000 characters limit?
  6. Adriaan New Member

    The limit is when using EXEC (which you can work-around by doing a EXEC (@var1 + @var2)) and I presume the same happens for sp_ExecuteSQL.
  7. chopeen Member

    But BOL says:
    Execute a character string
    { EXEC | EXECUTE }
    ( { @string_variable | [ N ]'tsql_string' } [ + ...n ] )
    [ AS { LOGIN | USER } = ' name ' ]
    [;]

    @ string_variable
    Is the name of a local variable. @string_variable can be any char, varchar, nchar, or nvarchar data type. These include the (max) data types.
    What am I misunderstanding here?
  8. satya Moderator

    Are you using UNICODE data?
    BOL
    Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length
  9. ranjitjain New Member

    Hi
    As u have defined the @sql variable with max so, I don't think you will ever hit the limit of 4000 chars as there is no limit in SQL 2005, so just above your exec statement, check or print the length of @sql variable to check how many chars it has saved, I am sure in some of the above statements @sql is being redefined and size of it is being changed from max to fixed length.
    I had faced the same issue once and I had to explicitly use convert to define the size as max.
    Also as suggested use varchar(max) instead
  10. Steven Mie New Member

    I have tried using the varchar(MAX), but in some instances I have 20,000 char's in the query and it gets truncated.
    Also when trying to do the EXEC(@var1 + @var2) it doesn't seem to work.
    Perhaps I am not doing the right syntax???
  11. Steven Mie New Member

    I have tried this with the EXEC....
    ALTER PROCEDURE [dbo].[SearchTenderMultiRegions]
    (
    @whereclause nVarChar(MAX), --4000 chars
    @PageIndex int,
    @PageSize int
    ) ASSET NOCOUNT ON;
    DECLARE @sql nvarChar(MAX)
    DECLARE @sql2 nvarChar(MAX) SET @sql= 'SELECT * FROM (SELECT DISTINCT TOP (' + CONVERT(nvarchar(10),(@PageIndex*@PageSize))
    SET @sql= @sql + ') ROW_NUMBER() OVER (ORDER BY Tenders.ID DESC)AS Row, Tenders.ID,Tenders.Title,Address.Suburb,Tenders.ClosingDateTime,Tenders.IsApproved,Tenders.TendersSourceID, Tenders.SourceDate,
    UserTracking.CreateUserID,UserTracking.CreateDateTime FROM Tenders LEFT JOIN TenderContact ON Tenders.ID = TenderContact.TendersID
    LEFT JOIN TenderCategory ON Tenders.ID = TenderCategory.TendersID
    LEFT JOIN TenderRegion ON Tenders.ID = TenderRegion.TendersID
    LEFT JOIN RegionStateCountry ON TenderRegion.RegionStateCountryID = RegionStateCountry.ID
    LEFT JOIN Address ON Tenders.ID = Address.TendersID
    LEFT JOIN UserTracking ON Tenders.ID = UserTracking.TendersID
    WHERE '--SET
    @sql= @sql + @whereclause
    SET @sql2 = @sql2 + ') as TenderSearchEntries
    WHERE Row between ('

    SET @sql2= @sql2 + CONVERT(nvarchar(10), @PageIndex)
    SET @sql2= @sql2 + ' - 1) * '
    SET @sql2= @sql2 + CONVERT(nvarchar(10), @PageSize)
    SET @sql2= @sql2+ '+ 1 and '
    SET @sql2= @sql2 + CONVERT(nvarchar(10), @PageIndex)
    SET @sql2= @sql2 + ' * '
    SET @sql2= @sql2 + CONVERT(nvarchar(10), @PageSize)EXEC
    (@sql+@whereclause+@sql2)
    ----and it doesnt work.
    I test the same query without the exec and it works if it doesnt get truncated.
  12. chopeen Member

    Are you sure that @sql+@whereclause+@sql2 contains a complete query and the EXEC command truncates it?
  13. alzdba Member

    - be sure your variables don't contain NULL !!
    - use print @sql to debug your stuff.
  14. Adriaan New Member

    Excellent point from alzdba. Note that in T-SQL, all variables that you declare have a default value of NULL.
    You have to do SET @MyVarchar = ''
    before you can do SET @MyVarchar = @MyVarchar + 'blabla'
    Finally, if you do use PRINT to debug, make absolutely sure that you remove all PRINT commands completely from the code that you put into production.
  15. Steven Mie New Member

    None of the values passed into the EXEC would have NULL.
    How does using print help me debug?
  16. alzdba Member

    If the print shows a non-null result, at least you can see _what_'s your actual generated sqlcommand.
    If the print shows nothing, you're stuck with a null in your variables [:S]
  17. chopeen Member

    Print the @sql variable just before EXEC(@sql) to make sure that the EXEC command is causing the problem i.e. that the @sql variable is not truncated earlier.
  18. consolidata New Member

    In your code above, you have not set the variable @sql2 to anything before trying to add to itself. You need set @sql2 = '' before that. Printing your statements when debugging helps a lot - thats how I saw what your problem was. We normally have
    --print (@sql + @whereclause + @sql2)
    exec (@sql + @whereclause + @sql2) in our code, so that we can interchange easily by commenting out the exec and uncommenting the print for quick debugging.
  19. Steven Mie New Member

    Thankyou so much. You are completely right.
    Thanks for looking at the code.

Share This Page