Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how can i use declare text in sql store procedure Expand / Collapse
Author
Message
Posted Wednesday, May 6, 2009 5:25 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 10, 2010 12:19 AM
Points: 8, Visits: 46
Hi ,

I am using a store procedure where i am creating a query dynamic , but query size is increasing the limit of varchar(8000) size , how i can use the data type text in the store procedure to store the created query and then run using the command Exec(@query)

I tried declaring it using the method below :

DECLARE @Query text(16000)

but giving me the error below :

Msg 131, Level 15, State 3, Procedure csplitsubtable_loop, Line 11
The size (16000) given to the type 'text' exceeds the maximum allowed for any data type (8000).
Post #710944
Posted Wednesday, May 6, 2009 5:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 10, 2011 6:11 AM
Points: 122, Visits: 93
Hi,

text datatype can have maximum length of 8000.
I think you should break your dynamic query into 2-3 parts.

Post #710950
Posted Wednesday, May 6, 2009 6:15 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 23, 2014 5:59 AM
Points: 329, Visits: 470
You cannot specify a column width on data type text.



Madhivanan

Failing to plan is Planning to fail
Post #710978
Posted Wednesday, May 6, 2009 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 10, 2010 12:19 AM
Points: 8, Visits: 46
nvarchar(max)

solved my purpose , thanks a lot !
Post #710998
Posted Wednesday, May 6, 2009 7:11 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Friday, July 25, 2014 3:11 PM
Points: 15,517, Visits: 27,898
pushpa.kumari (5/6/2009)
Hi,

text datatype can have maximum length of 8000.
I think you should break your dynamic query into 2-3 parts.



Text doesn't actually have a limit that low. Large Objects, LOB or BLOB for the binary large objects, are stored on disk, seperate from the table. However, use of the BLOB types are discouraged in 2005/2008.

The OP found the right answer, using VARCHAR(MAX) is the way to go. If you were working with a binary it would be VARBINARY (MAX).


----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #711034
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse