Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

Converting text to a varchar from a text cursor

We are trying to use string functions on information we retreive with a cursor. The problem is that the cursor is pulling information from a text column. We need to convert the information we pull from the text cursor into a varchar so that we may us the string functions on it. We can easily convert a single text entry into a varchar using the select and convert function but it seems to be a totally different case when trying to do this with a cursor. Hopefully we are just missing the obvious, but if anyone has a solution and if you could provide a detailed example we would be very appreciative. Thank you.

Examples:
(This is the cursor call, incidents.description is a text column)
declare incident_cursor cursor for
select incidents.description from incidents
where incidents.accountid = 185
open incident_cursor
declare @testing varchar(255)

(This fetch statement yields the following error)
fetch next from incident_cursor into @testing

Msg 16922, Level 16, State 1
Cursorfetch: Variable type of '@testing' does not match that of selected column in the cursor

I have tried many other ways of running this query and they all yield similiar results. If someone was able to do a cursor query on their own system and convert the results into a varchar and submit their method I would be eternally grateful.
0
kalum
Asked:
kalum
1 Solution
 
Victor SpiridonovCommented:
> but it seems to be a totally different case when trying to do > this with  a cursor.

Why? can you post an example of the problem?

0
 
vvkCommented:
In T-SQL you can't get more than 255 of text field to variable.
0
 
kalumAuthor Commented:
Edited text of question
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
kalumAuthor Commented:
Adjusted points to 200
0
 
formulaCommented:
I successfully did the following on my system:

declare test_cursor cursor for
select convert(varchar(255),#test_comp.Comp_Name) from #test_comp
where #test_comp.Comp_Code = "A1"
open test_cursor
declare @test_comp_name varchar(255)
fetch next from test_cursor into @test_comp_name

Looks like you need to convert the field to varchar(255) in the cursor definition "select" as follows:

select convert(varchar(255),incidents.description) from incidents where incidents.accountid = 185

Please let me know if this works on your system.

0
 
kalumAuthor Commented:
Dear Formula, that worked excellent.
Thank you very much for taking the time to solve our dilemna.
Here is the final revision that worked perfectly:

declare test_cursor cursor for
select convert(varchar(255),incidents.description) from incidents
where incidents.incidentid = 1
open test_cursor
declare @test_comp_name varchar(255)
fetch next from test_cursor into @test_comp_name
select @test_comp_name
close test_cursor
deallocate test_cursor

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now