DECLARE local variable for large test

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)

DECLARE local variable for large test

Author

Topic

drq
Starting Member

3 Posts

Posted - 2005-05-21 : 15:33:29

First off, I new at this. I'm trying to write in a script a large chunck of text to be displayed as a HTML page. Everything works except my field that has a large amount of text. I know from the reference guide that a local variable can not be ntext or text and should be a vchar, but even if I declare text as 'DECLARE @NarrativeTxt varchar(1000);' , I still only get 1 line of about 180 characters of text.
Any help would be appreciated.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-21 : 17:31:28

How are you displaying the text?
QUery analyser has a default limit of 256 chars for strings.
tools, options, results to change it.

You can build up a longer string in a single row temp table with a text datatype column.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

drq
Starting Member

3 Posts

Posted - 2005-05-21 : 17:45:28

The text is being used to be displayed as part of HTML (i.e.:

SET @strMessage = '<tr><td class="funcpagebody" colspan="4" align=left>'+IsNull(@NarrativeTxt,'')+'</td></tr>';
<%soutputreport("@strMessage")%>

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-21 : 20:43:09

My comment still holds.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

drq
Starting Member

3 Posts

Posted - 2005-05-22 : 08:28:53

I tried this:

Declare @getPointer varbinary(16)
Declare @NtsTxt varchar(8000)

SELECT @getPointer = TextPtr(NtsTxt)
FROM CaseNote
WHERE (NarrativeTypCd = 'CL') AND ActNm = 'drq'

SELECT TEXTVALID('CaseNote.NtsTxt', @getPointer)

SET @NtsTXT = ReadText CaseNote.NtsTxt @getPointer 0 1650

and I get the following syntax error:

Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ReadText'.
I really need to get the text in a local variable so I can build HTML on the client.

Thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2005-05-22 : 13:24:08

You can't use readtext to populate a variable - and why would you want to do that when a select will do it.

select @NtsTXT = substring(NtsTxt, 1, 8000) from CaseNote
WHERE (NarrativeTypCd = 'CL') AND ActNm = 'drq'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources