How to assing text field value to a variable (URGN

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How to assing text field value to a variable (URGN

Author

Topic

ikhuram
Starting Member

28 Posts

Posted - 2004-05-25 : 06:32:10

I want to assing text field value to a text type variable. But procedure does not allow to declare any text, ntext and image datatype variables. Please tell me what to do?

srinivasanr
Starting Member

15 Posts

Posted - 2004-05-25 : 06:46:14

Hi,
You can very well have "text" data type as one of the procedure's parameter...
Use CONVERT(nvarchar,@textfield) inside the procedure..
Wallops!!!

ikhuram
Starting Member

28 Posts

Posted - 2004-05-25 : 07:11:04

As you told
declare @output nvarchar
select @output = convert(nvarchar, tmp_test) from temp where tmp_id = '0014'
print @output

this shows only one character while there are around 25000 characters

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2004-05-25 : 07:17:34

The procedure accepts optional parameter, hence u can try this.

create proc usp_AssignTxt(@txtVal text='')
as
Select @txtVal = tmp_test from temp where tmp_id = '0014'
print @txtVal

Enjoy working

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-05-25 : 07:18:43

You have to specify a length for the nvarchar variable.

try this and see what you get

--****************************************
declare @a nvarchar
declare @b nvarchar(20)

set @a = 'abcdef'
set @b = 'abcdef'

select @a, @b
--******************

you must also remember that the maximum length of nvarchar is 4000 - this obviously will not be able to handle the 25000 characters that you have.

This can maybe be achieved by splitting the 25000 into 7 different nvarchar(4000) variables - it will be ugly though.

Duane.

ikhuram
Starting Member

28 Posts

Posted - 2004-05-25 : 07:25:10

create procedure sp_test (@input text)
as
begin
select @input = tmp_test from temp where tmp_id = '0014'
print @input
end

GOT ERROR:
Server: Msg 409, Level 16, State 1, Procedure sp_test, Line 4
The assignment operator operation cannot take a text data type as an argument.
Server: Msg 257, Level 16, State 1, Procedure sp_test, Line 5
Implicit conversion from data type text to nvarchar is not allowed. Use the CONVERT function to run this query.

Kristen
Test

22859 Posts

Posted - 2004-05-25 : 07:26:52

quote:
Originally posted by ikhuram

I want to assing text field value to a text type variable. But procedure does not allow to declare any text, ntext and image datatype variables. Please tell me what to do?

If you want to process 25,000 characters of data, from a TEXT column, in an SProc I don't think you have any choice other than to use TEXTPTR, READTEXT, WRITETEXT, and UPDATETEXT

Kristen

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2004-05-25 : 07:58:40

/* sample code for TextPt,readtext */
DECLARE @val varbinary(16)
SELECT @val = textptr(tmp_test ) from temp where tmp_id = '0014'
READTEXT temp.tmp_test @val 4 4

Sorry ikhuram, Assigning Text value to a text field is not possible as Kristen said U can opt for txtptr. But I dont think that will solve ur requirement.

Explicit conversion using the CAST function is supported from text to varchar, from ntext to nvarchar, and from image to varbinary or binary, but the text or image data is truncated to 8,000 bytes and ntext data is truncated at 4,000 characters (8,000 bytes). Conversion of text, ntext, or image to another data type is not supported, implicitly or explicitly

Cant u get the resultant from SELECT statement rather than returning the value in text variable .

Enjoy working

ikhuram
Starting Member

28 Posts

Posted - 2004-05-25 : 08:06:07

Thanks for the help. I want to get the whole column value into a variable and then manipulate it. e.g; if a developer sends a WHERE CLAUSE consisting of more than 8000 characters, that I will use with the select query after manipulation. How can I work on this issue. As within the procedure we can not use any string function on Text data type. Please tell me what to do for this?

Kristen
Test

22859 Posts

Posted - 2004-05-25 : 08:21:45

I'm guessing, but if the WHERE clause is bigger than 8,000 characters its probably because there is a long list of values to match - e.g. WHERE MyColumn IN (1,2,3 ... 1000000,1000001)

If this is the case maybe the Client Application could put all the "IN" values in a table of some sort and then you can just JOIN that table as oart of your FROM statement.

Otherwise the client application will have to pass the WHERE clause in a number of, not more than 8,000 characters each, parameters.


CREATE PROCEDURE MyProc
     @strSQL1 varchar(8000)='',
     @strSQL2 varchar(8000)='',
     ...

and then you've got soem sort of


    EXEC (@strSQL1 + @strSQL2 ...)

Note that it IS permited to concatenate string varaibales in the EXEC command such that the total is MORE than 8000 characters

(if you are using Nvarchar then the 4000 character limit applies in place of the 8000 I have shown)

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources