Fetch and cursor 1

[melginger]

Hi, i'm very new to programming with SQL. I'm good with query but now I have to transfer a function from my vb application to SQL something

In clear, I have a Select of two fields on multiple rows. What I want is to create in SQL what in VB I use to put one after each other every two fields of each row.



For instance:

 Select Data,Suffix FROM t1

gives:

Data Suffix
Green '-'
Red '-'


What I want my SQL to gives me is 'Green - Red -'

One problem I have is that I don't know how many lines my SELECT will return to me. For now I have made that code:

DECLARE @Userfield as varchar(100)
DECLARE @Suffix as varchar(100)
DECLARE @Title as Varchar(300)

DECLARE cuUF CURSOR FOR 

Select Userfield,Suffix FROM t1

OPEN cuUF 

FETCH NEXT FROM cuUF INTO @Userfield, @Suffix
WHILE  @@FETCH_STATUS = 0 
BEGIN
	set @Title= @Title + @Userfield + @Suffix
	FETCH NEXT FROM cuUF INTO @Userfield , @Suffix
END
	Select @title
CLOSE cuUF 
DEALLOCATE cuUF

But it's not working it returns me NULL.
When I test and write set @Title= @Userfield + @Suffix, It's ok, but It gives me only the last line (because it's what it supposes to do).

Anyway any help would be great.

Thanks

Mel

 

[jby1]

Try initialise Title before you go into the loop eg:

select @Title = ''

 

[donutman]

It returns a NULL because you didn't initialize @Title, so it starts as a NULL. Null+Anything=Null. However, the first thing to learn about SQL is to almost never use a cursor. Check this thread that compares a cursor to a set-based solution, thread183-956596.
You can solve your problem with as follows:

[Blue]DECLARE[/Blue] @Title [Blue]AS[/Blue] [Blue]varchar[/Blue][Gray]([/Gray]8000[Gray])[/Gray]
[Blue]SET[/Blue] @Title[Gray]=[/Gray][red]''[/red]
[Blue]SELECT[/Blue] @Title[Gray]=[/Gray]@Title[Gray]+[/Gray]Userfield[Gray]+[/Gray]Suffix [Blue]FROM[/Blue] t1
[Blue]SELECT[/Blue] @Title

You can make it one line shorter by using the COALESCE function, but this should be easier to understand.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]