SQL Server Performance

concatenate text to TSQL

Discussion in 'ALL SQL SERVER QUESTIONS' started by tarekj, Feb 13, 2012.

  1. tarekj Member

    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.
  2. Jon M Member

    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.
  3. tarekj Member

    Hi Jon,
    it is SQL Server 2005, I found nvarchar(max) also is limited to 8000, what do you suggest?
  4. FrankKalis Moderator

    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.
  5. tarekj Member

    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

Share This Page