hi - I used to use variables in SP as follows: CREATE PROCEDURE [dbo].[MyProc] (@Crit nvarchar(4000)='') AS Begin SET NOCOUNT ON; declare @SQL varchar(8000) SET @SQL='SELECT * FROM ITEMS' if NOT (@Crit='' OR @Crit IS NULL) SET @SQL=@SQL + ' Where ' + @Crit execute (@SQL) End and it is working fine as the criteria is optional, but due to the text limit I had to use direct cursor as follows CREATE PROCEDURE [dbo].[MyProc] (@Crit nvarchar(4000)='') AS Begin SET NOCOUNT ON; SELECT * FROM ITEMS End - it is working fine except that I am not able to add criteria part which is optional. Please help.
Hello tarekj, What version of SQL Server are you using? If that is 2005, you may use nvarchar(max) otherwise if it is versions 7 or 2000, you are limited to 8000. You may also want to use some parameter lists...check on sp_executesql.
nvarchar(MAX) is almost certainly not limited to 8000 characters What is happening is that you are assigning the result of a concatenation of a varchar(8000), a string constant, and an nvarchar(MAX) to a variable of type varchar(8000) which you then pass on to the EXEC() function. Try to be consistent in your choice of data types, as you'll see that you can use more then 8000 characters.
FOUND IT... it was due the limitation of varchar(Max), I had to cast the concatenated variable to (MAX): Limitation of varchar(Max) ---------------------------------------------------------- 1) If a variable hold more than 8000 character. But in print option it display only 8000 character. We should use select statement instead of print. 2) If you add a literal value in a variable of varchar(max) datatype without casting. It will only store 8000 character. See the below example. Declare @tst Varchar(max) Set @tst = space(7998) + 'test' Select len(@tst) Output --------- 8000 But when you add literal value after casting, it can hold more than 8000 characters. See the below example. Set @tst = space(7998) + CAST('test' as varchar(max)) Select len(@tst) Output -------- 8002
