return value more than 8000 characters from stored procedure

• sunil_ranade

  SSC Veteran

  Points: 265

  More actions

  March 11, 2007 at 9:30 am

  #68462

  Hi,

  I want to return one dynamic string from stored procedure. The length of string is not fixed. It can be less than 8000 characters or more than that. There is no limit to length of this string.

  I don't want to use multiple varchar output parameters because length of string is unpredictable. So how do I return this string from stored procedure.

  1. I tried with Text datatype. But I got to know that I can not assign value runtime to Text datatype variable/parameter.

  2. I tried to use new datatype in sql server 2005 called varchar(max).  But I am not able to create output paramter with this datatype. I got an error "Incorrect syntax near 'max'"

  Can anybody please help me...Thanks in Advance.

  Regards,

  Sunil

   

   

  
  Software Programmer

• bledu

  SSChampion

  Points: 12335

  More actions

  March 13, 2007 at 9:18 am

  #694722

  can you paste the top part of your stored proc, you might have made a mistake.

  I was able to execute the script below and return 19000 characters. you can try it for more

  CREATE PROCEDURE cp_test8000

  -- Add the parameters for the stored procedure here

  @out VARchar(MAX) OUTPUT

  AS

  BEGIN

  DECLARE @limit INT

  DECLARE @counter INT

  DECLARE @outer VARCHAR(MAX)

  SELECT @counter = 1,@limit = 5000, @outer = ''

  WHILE @counter < @limit

  BEGIN

  SELECT @outer = @outer + 'baba', @counter = @counter + 1

  END

  SELECT @out = @outer

  END

  GO

  --- to run the stored proc ----

  DECLARE @rc int

  DECLARE @out VARCHAR(max)

  -- TODO: Set parameter values here.

  EXECUTE @rc = [dbo].[cp_test8000]

  @out OUTPUT

  SELECT LEN(@out)

  ---

  i did a cheeky test for @limit=100,000 and i got a length of 399,996

  ---

  Everything you can imagine is real.