Assign value to variable of data type ''Text''

Question

Assign value to variable of data type ''Text''

Hariharan-202345

SSC Eights!

Points: 821
More actions
March 29, 2005 at 2:37 am

#68809

Hi guys,
In SQL Server, assignment operator operation cannot take a text data type as an argument. Does anyone know how to assign values to variables of 'text' data type?
Your help much appreciated.
Thks,
Hari

Kindest Regards,
Hari

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

With SQL 2000, the only way to assign a text datatype to a variable is to pass it in as a parameter to a stored procedure.

You can not do something like:

DECLARE @var text
SELECT @var = textcol from table

or anything like that. Have to wait for SQL 2005 for varchar(max) to get this kind of functionality.

Scott Thornburg

Out For Justice SSCrazy Points: 2733 More actions · Answer 2

I don't know if this is of any help, but I have got round the problem of not being able to assign values to text data type variables by using a table variable.

Here is an example that searches a text string for IDs that exist in another table:

-- create the table variable

DECLARE @Text1 table (MyValue text collate latin1_general_CI_AS not null)

INSERT @Text1 (MyValue) values ('ACDEFGHIJKLMNOPQRSTUVWXYZ')

-- now create a table with a list of IDs

CREATE TABLE IDList (MyID varchar(10) not null, MyName varchar(50) collate latin1_general_CI_AS Not null )

INSERT IDList VALUES ('A', 'Name1')

INSERT IDList VALUES ('B', 'Name2')

INSERT IDList VALUES ('C', 'Name3')

-- Find all IDs that were in the text variable

select * from IDList, @Text1 as spam

WHERE spam.myvalue Like '%' + IDList.MyID + '%'

DROP TABLE IDList

-- ENDS

Obviously it depends on what you want to do with your value as to whether this method will work for you, but it does allow you to use a text value to some extent.

Hariharan-202345 SSC Eights! Points: 821 More actions · Answer 3

thanks, but what am looking for is slightly different from the solutions you have given.

background:

my asp pg has a textarea, where in user will input a large amount of data (usuall users will copy columns from excel and paste it in the textarea and the no.of rows may be over 10,000). i send the user input to my sp as a input parameter of type text.

user input:

11111

22222

33333

444444

...

..

10000000

in my sp, i have to manipulate this 'text' variable and insert the individual values into a table. for achiving that i need to loop through the 'text' variable inorder to get each value from the whole string.

let me know if you are still not clear

Kindest Regards,

Hari

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

You cannot declare a variable of type text!

And I think your approach here is prone to errors. How will you catch all exceptions when user use this way to upload the data. One user might use 1,2,3,4,5, another one maybe 1 2 3 4 5 and a third 1;2 3-5,6 or whatsoever.

I would really consider offering an file upload functionality where user can upload xls docs. It's much easier to deal with the Excel format than with all possibilities such an ASP textarea offers.

Anyway, for arrays and such looping through string you might want to have a look at http://www.sommarskog.se/arrays-in-sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Hariharan-202345 SSC Eights! Points: 821 More actions · Answer 5

Thanks friend.

Since the user will just copy the data from Excel and paste in the text area, each line will be separated by chr(13) or chr(10) and so i replace it with ",". so thatz not a major issue. this report am working on is a frequently used report and i cannot expect the user to upload an excel file each time.

i will look at the URL on SQL Arrays.

Thanks for your time,

Hari

Kindest Regards,

Hari

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Good luck and hopefully all users will remember to copy and paste from Excel.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Out For Justice SSCrazy Points: 2733 More actions · Answer 7

The danger of copy and pasting from Excel is that there may be a number that is formatted with commas in it, e.g. 1,000,000.00 which will throw off your routine if you are using comma as a delimeter.

peterhe SSChampion Points: 11469 More actions · Answer 8

since the lines in the text area are delimited by char(13) and Char(10), you can use thse two chars to parse the text into a table.

the following sample sp does this.

IF EXISTS(SELECT 1 FROM sysobjects WHERE ID=OBJECT_ID('dbo.usp_GetLinesFromText') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

DROP PROCEDURE dbo.usp_GetLinesFromText

GO

CREATE PROCEDURE dbo.usp_GetLinesFromText @Text ntext, @Delimiter nvarchar(10)

AS

DECLARE @Lines TABLE (Line nvarchar(4000),LineOrder int)

DECLARE @LineOrder int

DECLARE @DelimiterLen int

DECLARE @Pos int

DECLARE @OldPos int

SELECT @LineOrder=1,@OldPos=1,@DelimiterLen=LEN(@Delimiter)

select @DelimiterLen

SET @Pos=ISNULL(CHARINDEX(@Delimiter, @Text),0)

WHILE @Pos > 0

BEGIN

INSERT INTO @Lines VALUES(SUBSTRING(@Text, @OldPos , @Pos - @OldPos),@LineOrder)

SET @LineOrder=@LineOrder + 1

SET @OldPos=@Pos+1

SET @Pos=ISNULL(CHARINDEX(@Delimiter, @Text, @OldPos),0)

END

SELECT LineOrder,Line FROM @Lines

GO

inside your main sp, call this one:

create table #Lines (Line nvarchar(4000),LineOrder int)

DECLARE @Delimiter nvarchar(10)

SET @Delimiter=NCHAR(13)+NCHAR(10)

insert #Lines (LineOrder,Line)

EXEC dbo.usp_GetLinesFromText @Text, @Delimiter

where @Text is the text you got from the call asp page made.

Good luck.