Assign value to variable of data type ''Text''
-
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
-
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.
-
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
-
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] -
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
-
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] -
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.
-
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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply