Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-11-20 : 09:14:08
|
| Biddu writes "Hi, I would like to use text datatype in a stored procedure to hold a very large data. I cannot use varchar as it is limited to 8000 only. I get an error when I use "stuff" & "left" function in the stored procedure. The error is "The assignment operator operation cannot take a text data type as an argument." Here's the code of the stored procedure I am trying to create. It works fine with datatype as varchar.Stored Procedure-----------------CREATE procedure cp_Test( @Array text, @separator char(1)=',' ASset nocount on-- @Array is the array we wish to parse-- @Separator is the separator charactor such as a commadeclare @separator_position int -- This is used to locate each separator characterdeclare @array_value varchar(1000) -- this holds each array value as it is returned-- For this loop to work we need an extra separator at the end. -- Look to the left of the separator character for each array value.--set @array = @array + @separator-- Loop through the string searching for separtor charactersselect @intUpdated=0while patindex('%' + @separator + '%' , @array) <> 0 begin -- patindex matches the a pattern against a string select @separator_position = patindex('%' + @separator + '%' , @array) select @array_value = left(@array, @separator_position - 1) -- This is where you process the values passed. -- @array_value holds the value of this element of the array Begin -- SQL End select @array = stuff(@array, 1, @separator_position, '')endset nocount offRETURNGO----------------------------------------------Could you please help me solve this problem.Thanks in advance.Biddu." |
|
|
|
|
|
How to use text datatype with left & stuff functions.