Convert + add all field data for insert into one field 1

[puitar]

Greets,

The following procedure is designed to insert a record into an audit table. I have to convert some of the fields to varchar type so I can add them together to form one big string to insert into the DATA field. I get an error message if I don't.

The problem is that when I convert the 'ntext' type fields to varchar they are cut down to a size of about 20 characters. I thought varchar could handle thousands of characters.

I hope I have explained the well enough.

'''''''''''''''''''''''''''''''''''''''''''''''''''
ALTER PROCEDURE inetPromanAuditAdd
(

@pUsername varchar(50),
@pOperation varchar(50),
@pProgramID int


)
AS

INSERT INTO audit ( username, operation, ProgramID, ProgramTitle, DATA )

SELECT @pUsername, @pOperation, @pProgramID, ProgramTitle, '[' +
isnull(convert(varchar,ProgramNeed),'')+']['+
isnull(convert(varchar,Daate),'')+']['+
isnull(TargetAudience,'')+']['+
isnull(convert(varchar,ProgramOverview),'')+']['+
isnull(convert(varchar,ReqLearningOutcomes),'')+']['+
isnull(convert(varchar,ImplementationProcess),'')+']['+
isnull(convert(varchar,ResourcingRequirements),'')+']['+
isnull(convert(varchar,EvaluationMethodology),'')+']['+
isnull(NextEffectivenessEvaluation,'')+']['+
isnull(convert(varchar,PRCAdvice),'')+']['+
isnull(convert(varchar,Recommendation),'')+']['+
isnull(convert(varchar,RPAFrom),'') +']['+
isnull(convert(varchar,RPATo),'')+']['+
isnull(ProgramType,'')+']['+
isnull(Location,'')+']['+
isnull(convert(varchar,ProgramDeveloper),'')+']['+
isnull(DeveloperPhoneNo,'')+']['+
(convert(varchar,Approved))+']' AS DATA
FROM programs
WHERE id=@pProgramID
''''''''''''''''''''''''''''''''''''''''''''''''''''''

HELP!

 

[tlbroadbent]

You need to declare the length of the Varchar data type in the Convert function. If you don't declare it, the defauilt length is 30 characters.

Example:

isnull(convert(varchar(500),ProgramNeed),'')+']['+
isnull(convert(varchar(10),Daate),'')+']['+
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[puitar]

OUTSTANDING!!!!
thanks