|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Exceeds Variable Length - Varchar(8000)what is the best way to handle a situation, if the variable value (Parameter
of Varchar 8000) exceeds 8000 char, while passing from the front end to a SQL Procedure. Using 'Text' type is a solution, but is there any other way to handle it. Well, what do you want to do? Save the text > 8000 somewhere else, or
discard it, or another option? Show quote "Paul" <P***@discussions.microsoft.com> wrote in message news:79560883-A9FE-4C42-9F6B-B20D68965458@microsoft.com... > what is the best way to handle a situation, if the variable value > (Parameter > of Varchar 8000) exceeds 8000 char, while passing from the front end to a > SQL > Procedure. Using 'Text' type is a solution, but is there any other way to > handle it. I declared a parameter of varchar(8000) in the stored procedure which will
accept the value from front end. Now the value that is coming in is exceeding 8000. I am thinkig of changing the parameter type to 'text'. But text has got some limitations while manipulating the value in teh stored procedure. It is not flexible as varchar. Is there any other way.. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > Well, what do you want to do? Save the text > 8000 somewhere else, or > discard it, or another option? > > > > "Paul" <P***@discussions.microsoft.com> wrote in message > news:79560883-A9FE-4C42-9F6B-B20D68965458@microsoft.com... > > what is the best way to handle a situation, if the variable value > > (Parameter > > of Varchar 8000) exceeds 8000 char, while passing from the front end to a > > SQL > > Procedure. Using 'Text' type is a solution, but is there any other way to > > handle it. > > > >I declared a parameter of varchar(8000) in the stored procedure which will Any other way to WHAT? Do you need to keep more than 8000 characters or > accept the value from front end. Now the value that is coming in is > exceeding > 8000. I am thinkig of changing the parameter type to 'text'. But text has > got > some limitations while manipulating the value in teh stored procedure. It > is > not flexible as varchar. Is there any other way.. not? If not, FIX THE FRONT END! If so, then change the column to be TEXT and the parameter to be TEXT. I guess the front end is passing a string that is more than 8000 charecters,
and he wants to capture that value with in a stored procedure and do some string manupulations(like replace etc..) and use it some where with in the procedure. I am also looking for a solution to the similar problem. Show quote "Aaron Bertrand [SQL Server MVP]" wrote: > >I declared a parameter of varchar(8000) in the stored procedure which will > > accept the value from front end. Now the value that is coming in is > > exceeding > > 8000. I am thinkig of changing the parameter type to 'text'. But text has > > got > > some limitations while manipulating the value in teh stored procedure. It > > is > > not flexible as varchar. Is there any other way.. > > Any other way to WHAT? Do you need to keep more than 8000 characters or > not? If not, FIX THE FRONT END! If so, then change the column to be TEXT > and the parameter to be TEXT. > > > >I guess the front end is passing a string that is more than 8000 You can easily break apart such a string using SUBSTRING()... but you need >charecters, > and he wants to capture that value with in a stored procedure and do some > string manupulations(like replace etc..) and use it some where with in the > procedure. I am also looking for a solution to the similar problem. to put a practical limit on the amount of data the front end can pass in. Constructing this to cover 2GB of data is going to be pretty annoying. ALTER PROCEDURE dbo.DoStuffWithMoreThan8000Chars @value TEXT AS BEGIN SET NOCOUNT ON IF DATALENGTH(@value) > 8000 BEGIN SELECT 'more than 8000 characters' SELECT SUBSTRING(@value, 1, 8000), SUBSTRING(@value, 8001, 8000), SUBSTRING(@value, 16001, 8000) END ELSE BEGIN SELECT 'less than 8000 characters' SELECT @value END END GO  |
|||||||||||||||||||||||
Other interesting topics