use AdventureWorks;
go

select isnull(convert(varchar(max), [Document], 1), 'null') blob from Production.Document;

sp_helptext 'master.dbo.fn_varbintohexstr'

sp_helptext 'sys.fn_varbintohexsubstring'

if ( ((@cbytesin * 2) + 2 > 4000) or ((@cbytesin * 2) + 2 < 1) )
return NULL -- @cbytesin is the datalength of the varbinary argument

create proc dbo.usp_generate_inserts
(
@table nvarchar(255),
@excludeCol varchar(2048)
)
as
begin
set @excludeCol = ',' + @excludeCol + ','
...

where 
	table_name = substring(@table, charindex('.', @table) + 1, len(@table)) 
	and data_type != 'timestamp'
	and not(@excludeCol like'%,' + column_name + ',%')
	order by ordinal_position;

exec usp_generate_inserts 'dbo.tableName', 'col1,col2,...'

if ( ((@cbytesin * 2) + 2 > 4000) or ((@cbytesin * 2) + 2 < 1) )
return NULL -- @cbytesin is the datalength of the varbinary argument

--the output limit for nvarchar(max) is 2147483648 (2^31) bytes, that is 1073741824 (2^30) unicode characters  
  if ( ((@cbytesin * 2) + 2 > 1073741824) or ((@cbytesin * 2) + 2 < 1) or ( @cbytesin is null ))  
   return NULL  

use AdventureWorks;
go

set nocount on;
go

create table dbo.test (col1 int not null, col2 text);
insert into dbo.test (col1, col2) values (1, 'sample 1,sample 1');
insert into dbo.test (col1, col2) values (2, 'sample 2,sample 2');
go

exec dbo.usp_generate_inserts 'dbo.test';
go

set nocount off;
go

set nocount on;
go

delete dbo.test;
go

-- this does not work, there is a good reason why Microsoft recommends 
-- not to use the text data type, but switch to varchar(max) instead
insert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);
insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);
go

alterz table dbo.test alter column col2 varchar(max);
go

-- this works
insert into dbo.test ([col1], [col2]) values (0x00000001, 0x73616d706c6520312c73616d706c652031);
insert into dbo.test ([col1], [col2]) values (0x00000002, 0x73616d706c6520322c73616d706c652032);
go

-- now revert the table back to what it was
alterz table dbo.test alter column col2 text;
go

select * from dbo.test;
go

set nocount off;
go