Which data type support max characters

[?]i have a stored procedure with output variable with varchar(4000),but my data is more than 4000 characters,so what datatype i should use to get the output.

Rajeev Kumar Srivastava
--ALWAYS BE POSITIVE!--

return a table


Cheers,
Preethiviraj Kulasingham

hi preethi
Can u give me example.



Rajeev Kumar Srivastava
--ALWAYS BE POSITIVE!--

Within your so Declare a temp table (or if you are using sql2000 use table variable)


Delcare @Temp Table (a Varchar(4000))


and devide your result into 4000 byte chunks
and Insert into the table


Finally select * from @temp


Note: you can return upto 8000 bytes if you are using varchar. 4000 byte limit if you use nvarchar.

Cheers,
Preethiviraj Kulasingham

No other way, I mean without dividing the result.
Becasue i don#%92t know the length of my variable, it is
Definatly more than 6000 characters.And What if my
data is more than 20000 characters.


Rajeev Kumar Srivastava
--ALWAYS BE POSITIVE!--

Just a word of caution, TEXT datatypes are slower in performance.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

[<img src='/community/emoticons/emotion-2.gif' alt='' />] When i use the datatype "TEXT" then it give Error:<br /><br />Server: Msg 2739, Level 16, State 1, Procedure GetText, Line 0<br />The text, ntext, and image data types are invalid for local variables.<br /><br /><b>So,how can i use "Text" datatype as output variable in any Stored Procedure.</b><br />eg:<br />create Proc P1<br />as<br />declare @B text<br />EXEC sp_XYZ @B OUTPUT <br />select @B<br /><br />Rajeev Kumar Srivastava<br /> --ALWAYS BE POSITIVE!--

Hi satya
Suppose if i use "Table" datatype then i have to divide result into 4000 byte and Insert into the table. Again there is a problem for dividing the whole data into 4000 byte because it will cut the data after 4000 byte, that is if last word is "Hello", till "e" we are completing 4000 bytes it will cut it till "he" and insert one new row with "llo".So how to overcome all this problems.




Rajeev Kumar Srivastava
--ALWAYS BE POSITIVE!--

Thanks.
Can u give me example.




Rajeev Kumar Srivastava
--ALWAYS BE POSITIVE!--