ntext in dynamic query

• Orlando Otero

  SSC Enthusiast

  Points: 102

  More actions

  June 6, 2005 at 2:28 pm

  #76329

  Hi all,

  I have a SP that builds and executes a dynamic UPDATE based on inputed parameters. Some parameters can be NULL, and if a parameter is null, it will not appear in the query

  UPDATE aTable

  SET ...

  WHERE pk=aTablePK AND P1=V1 --P1 is not null, and so on

  the problem is that one of the fields that must get updated is ntext and it can't be concatenated using the + operator, and while trying to use SUBSTRING, If the ntext field is setted with s string of more than 4000 chars, I receive an error .

  What are the best practices to code this kind of situation?

  Regards, OO

  Sorry about my english

• Lowell

  SSC Guru

  Points: 323518

  More actions

  June 6, 2005 at 3:16 pm

  #563349

  I would do it as a separate operation using the UPDATETEXT function;

  here's an example that might give you some insight, but go for the BOL to get otehr examples.

  Create Table #CreateStatements (uid int identity(1,1),Info text)  

  insert into  #CreateStatements (Info ) values ('some original values.')

  DECLARE @ptrval binary(16),@txtlen INT ,@CreateStatement varchar(128)  

  set @CreateStatement   ='some sample text to append to a text field.'

    if len(@CreateStatement) > 0     

    BEGIN     

     SELECT @ptrval = TEXTPTR(info) ,     

     @txtlen = DATALENGTH(info)     

        FROM #CreateStatements     

           WHERE uid=1

     UPDATETEXT #CreateStatements.info @ptrval @txtlen 0 @CreateStatement     

    END 

  select * from #CreateStatements

   

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!