SQL Server Performance

Which data type support max characters

Discussion in 'General Developer Questions' started by rajeev_id, Apr 19, 2004.

  1. rajeev_id New Member

    [?]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!--
  2. preethi Member

    return a table


    Cheers,
    Preethiviraj Kulasingham
  3. rajeev_id New Member

    hi preethi
    Can u give me example.



    Rajeev Kumar Srivastava
    --ALWAYS BE POSITIVE!--
  4. preethi Member

    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
  5. rajeev_id New Member

    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!--
  6. satya Moderator

    If the result is below 8000 characters then use VARCHAR, otherwise can take help of TEXT datatype.
    Refer to books online for more information.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. gaurav_bindlish New Member

    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.
  8. rajeev_id New Member

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />] 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!--
  9. satya Moderator

    Apologies for my oversight about recommendation by Preethi, its better to use TABLE datatype (since SQL 2K) if the @variable length is unknown.

    Table datatype -A special data type that can be used to store a result set for later processing. Its primary use is for temporary storage of a set of rows, which are to be returned as the result set of a table-valued function.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  10. rajeev_id New Member

    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!--
  11. satya Moderator

  12. rajeev_id New Member

    Thanks.
    Can u give me example.




    Rajeev Kumar Srivastava
    --ALWAYS BE POSITIVE!--

Share This Page