How do you construct a Dynamic Stored Procedure with more than 8000 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
) 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)

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?

SQL Server 2005

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.

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

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.

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?

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

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???

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.

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

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

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.

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

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]

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.

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.

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