How Do You Construct A Dynamic Stored Procedure With More Than 4000 Chars

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
) AS



SET 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)